Posted by: Denny Cherry
Backup & recovery, BACKUP DATABASE, Restore Database, Testing
I bet that if asked when the last time the database backup was taken and if it succeeded you would know off of the top of your head when that was. But if I asked you when the last time your database was restored successfully would you know?
Taking backups is obviously very important, if the database server fails or becomes corrupt you need the backup to restore the database. But how sure are you that your database restore will work correctly? Have you tested your backups recently? You should be testing your backups at least once a month if not more often.
Personally on my system I take full backups daily, and I test my backups every other day. In my case this is fully automated, which is how and why it is down so often. In my case I backup my database to disk at midnight, then do transaction log backups every 15 minutes. I keep three days worth of backups on the disk. Every two days our EMC storage array takes the disk which has the backups on it and makes a clone of it. When the clone is completed (which takes about 12 hours as data is being written to the disk every 15 minutes) the copy is mounted to another SQL Server. After the disk is mounted the storage array runs a batch file which kicks off sqlcmd and tells the SQL Server to start a job which runs the restore script, and then runs a DBCC CHECKDB to ensure that there isn’t any corruption in the database. The SQL Server portion of this takes about 26 hours to complete. About 12 hours later the entire process starts over again.
I keep each clone of the backup disk around until 2 successful clones have been taken which gives me 8-9 days of database backups available on disk over the 3 clones and the active drive.
While my process is probably overkill it was easier to setup the system to do the restore every other day than to tell it to only do it every few runs, and its better to error on the side of caution. As long as I’ve got the extra IO available on the RAID array that has these disks I don’t see any reason to run the process less often.