2 Responses to TechNet Magazine: feature article on recovering from disasters using backups

Love the articles! I have a question concerning the storage of backups and therefore the recovery phase as well. In my current environment, I have a weekly full, daily diff, and 15 minute transaction log for the most mission critical databases. I set up a backup "device" in SQL which maps to a UNC path on a NAS server as one single BAK file. I have the full backup fire on Sunday which creates the BAK file. The dailies and transaction backups append to this BAK file. Then on the next Sunday I have the full overwrite the existing BAK file. The full backup fires from an SSIS package; the first step in that package is to run CHECKDB and log to a TXT file. I have that step wrapped in an SQL Job (which should fail if CHECKDB fails; based on one of your blog entries). This first step must succeed for the full backup job to fire.

I have verified that I can restore from the backup device to point in time within my 15 minute RPO. Can you provide some reasons why using a backup device and saving all backups to this device (single file) is a bad idea?

You can store everything in a single file but it can be more complicated to restore from (for someone who doesn’t know what you’ve done) and it’s prone to someone accidentally using WITH INIT or WITH FORMAT. It’s also harder to make multiple copies as you need to copy the whole file to a secondary location after each backup is appended to it. You should not overwrite the previous file with the next full backup – you must keep several week’s worth of backups around in case your latest backup becomes corrupt.