SQLServerCentral Article

When is a Backup Considered Successful?


As a Database Administrator, we are responsible for backing up and restoring databases.  Many would argue that this fundamental task is the most important task of a DBA.  There have been many articles written and published on this forum about this topic and the intent of this article is to reinforce and stress the importance of database backups and restores.  So when can a DBA consider a database backup completely successful? When it’s Restored!

All DBA’s have processes and jobs in place to backup their databases.  In most cases, FULL database backups are performed on a daily basis and periodically throughout the day TRANSACTION LOG and perhaps a DIFFERENTIAL BACKUP is also performed.  As a DBA, do you have a process in place to restore the backups on a recurring basis?  Here are some items to consider so when it’s “game time”, you’re on your “game”.

Do you routinely practice restoring your backups to different servers? 

On a daily or weekly basis you should randomly select some backups and restore them to a different server.  This will validate your process and confirm your backup.  Not that you need any more practice, but the more restores the easier it gets.  You’ll end up finding the best techniques and realize what other scripts you may need such as “fixing orphaned logins”, etc.  This is also helpful for practicing your disaster recovery procedures.  Repeated tasks become innate tasks.

Do you routinely restore the backups that have been copied to tape?

How often has someone asked you to restore a database from “Wednesday night, of two weeks ago”?  At my business, I have to get the backup from tape.  Now I don’t directly get the backup; I have to put a request in to the platform unit person for the backup to be retrieved from tape.  My request usually takes several hours to be fulfilled and doesn’t always have the expected outcome.  On many occasions I have received a phone call saying that “We don’t have the Wednesday backup on tape; we have Friday night’s”.  Now what good does that do me? 

Additionally you may want to restore a backup from tape which is located in an “off site” location.  This will prove your entire backup strategy.  When was the last time you requested backups from your “off site” vendor?  Do you know who to contact?  What kind of timeframe does the vendor have to return the tape to you?

Some would rightly argue that there should unequivocally be reliable tape backups, and the people responsible for the tape system should perform at a higher level.  But, I can see tapes going bad and corruption happening, so I’ll give them the benefit of the doubt.  The reality of the situation is this wouldn’t have been discovered without the DBA actually requesting the backup from tape which leads me to my last item.

Do you have all the necessary “contact” information for personnel that you may need during a recovery process?

When it’s time to restore production for “real”, you shouldn’t have to scramble to locate the contact information that you may need for this process.  This information should be readily available on your PDA/Phone, as a file on your PC, and even a “hard copy” in your briefcase.  In the event of a full disaster recovery, you want detailed documentation for the recovery process including vendor information.  By doing so you will ensure you haven’t forgotten anything during a stressful situation.

Hopefully these brief words are nothing new to you, but if they are then take a moment and think about your recovery process then think about scheduling some time to restore a few databases.


4.06 (33)




4.06 (33)