The November 2009 edition of TechNet Magazine is up on the web and contains my latest feature article, the second in a 3-part series on backups/restores/repairs.
In this article I explain all about using the RESTORE command. Not much point having backups if you don't know how to use them! Topics include:
The four phases of a restore operation – how they work and how you can speed them up
Figuring out what you need to restore when a disaster happens
Figuring out what you're actually able to restore
Using WITH RECOVERY / NORECOVERY / STANDBY
Point-in-time restore operations
Considerations when restoring to a different location
There seem to be a few formatting issues when the magazine's web folks put the article up – I've notified them and hopefully they'll fix them soon.
You can get to the article at: http://technet.microsoft.com/en-us/magazine/ee677581.aspx
2 thoughts on “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.