Do I actually need to backup the installed SQL Server program files?

[edutech4schools]

Trying to trim our remote backup size as it's taking ages to backup and noticed we have C:\Program Files\Microsoft SQL Server getting backed up each night.

 

We do export and backup the Sims & Fms databases each night, so is there any need in backing up all the files in the SQL Server folder?

 

If I follow the path C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL I get to the following folders,

Backup

Binn

DATA

.......

 

I notice in the DATA folder I have the .ldf &.mdf database files, are these actually usable if something went wrong with our normal nightly Sims & FMS export backups?

[matt40k]

Depends.

 

Short answers, should be ok to exclude, check with your Support Team, its what you pay for them for - longer answer

 

The data folder is failsafe, yes its the raw database files, but it might be a unclean copy, it might have "files" open (wrong word files but its Monday) - admittedly SQL is transactional so its be v.unlucky if you couldn't use it. Backup is the default folder for SQL backups - so depends how its setup. If its actually creating backups else where then should just be junk.

 

I'd say that was poorly setup IMHO. For starters, why is SQL installed into C: drive. If the LDF grows out of control and fills the disc you won't be able to boot - not too much trouble for a virtual, but a physical, you're going to kiss good bye to a day to fix it.

[jinnantonnixx]

As @matt40k says, you should spend some time configuring your server according to best practices. That entails getting the most out of your disk subsystem, putting the OS, data, log and tempdb files on separate LUNs/disks.

 

Does your backup system actually work? The golden rule is: if you haven't proven your backups by successfully restoring to a different machine - and you can access your data - then all you have are 'alleged backups'. This should be your primary concern. Downtime is OK, data loss is not OK.

The MDF and LDF files are the working, live database files. You should not consider them to be part of your backup.

 

As for backing up the actual SIMS SQL server software, any good backup software would see these static files as unchanging and only copy them during a full backup.

Edited January 11, 2016 by jinnantonnixx

[Banjo]

I notice in the DATA folder I have the .ldf &.mdf database files, are these actually usable if something went wrong with our normal nightly Sims & FMS export backups?

 

Yes they would be usable as a last resort. They wouldn't have been detached properly from SQL but if they were all you had you could send they off to Capita to fix and get most of your data back.

[twin--turbo]

File level backup should not be done of a live SQL database. It may fail or take a very long time as the db file can't be fully locked to get a backup.

 

Veritas BE has SQL agents, Veeam has "Application" job types.

 

TT