SQL Scripts: How To Make Sure Your Backup Is Valid

Posted on by

I recently had a case where I needed to download a database backup to my laptop.  It usually takes about 12 hours for this particular backup to copy down, so I started it at the end of the day to let it run over night.  Unfortunately, when I sat down to start work the next day I discovered that my laptop was turned off.  Sometime during the night it had gotten OS updates and then shut down.

Once I got my laptop up I checked to see if my backup download completed before the laptop shut down.  I saw that the backup had been downloaded, and the file was the full size of what I would expect.  However the paranoid DBA in me does not trust this, so I ran a quick RESTORE VERIFYONLY check to see if the backup was indeed valid.  This check is a simple one line of code:

This command does not restore the backup, it just checks to see if the backup is complete and that the entire backup is readable.  RESTORE VERIFYONLY is very useful, and very important to run.  I run this command as part of my regular backup routines (it’s built in to the maintenance solution that I use).

Running RESTORE VERIFYONLY produced the following output, which confirmed what I suspected:

The file on device ‘C:\backups\MyDB.bak’ is not a valid Microsoft Tape Format backup set.

My backup was not valid.  Running RESTORE VERIFYONLY on the original backup on the server showed that it was fine, so my laptop must have shut down before my backup download completed, corrupting the file.

In my particular case the fix was easy, I just downloaded the backup again (and ensured my laptop stayed on this time!)

A positive result from RESTORE VERIFYONLY does not mean check your database for corruption, and it does not attempt to verify the structure of your data.  Even if RESTORE VERIFYONLY says your backup is good, in order to know if your backup (and database) is truly valid you need to restore it.  If I were to attempt to restore my bad database backup, I would have seen the following error:

The file ID 1 on device ‘C:\BACKUPS\MYDB.BAK’ is incorrectly formed and can not be read.
RESTORE DATABASE is terminating abnormally.

 

This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.

 

(Visited 7,000 times, 4 visits today)