Taking backups is good, but testing them is better.

Published On: 2010-02-02By:

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.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link