Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Right now, we test our SQL backups once a month by pulling the most recent backup file and restoring it onto a test database. If SQL Server says the restore is successful, we spot-check a few tables to make sure they have data in them.

Is it safe to assume that the restore was completely successful if SQL Server says it was? Is there a good way to verify that the restored data/indexes/etc are correct?

share|improve this question
    
Note: I would not restore once a month. I would restore, fully automated, every day. How much time do you want to elapse before you discover that your backup process has been failing? Is almost a month of failed backups acceptable to your business? –  ErikE Apr 5 '12 at 21:30

2 Answers 2

up vote 3 down vote accepted

It is considered good practice where I work to run DBCC CHECKDB after a restore, especially if the backup is of an unknown quality.

CHECKDB will at least tell you if your DB has any consistency errors, and checks the logical and physical integrity of all the objects in the specified DB.

A similar thread is on ServerFault: http://serverfault.com/questions/187869/should-i-run-dbcc-checkdb-before-full-backups-or-after

share|improve this answer
1  
Along with what Martin said, you can run RESTORE VERIFYONLY to see if you bak file is in a consistent state. –  darwindeeds Jan 3 '12 at 18:19
    
Thanks Martin. We run DBCC CHECKDB in our maintenance scripts, but I hadn't thought of using it to verify the restores. This is exactly what I was hoping to find. –  Josh Yeager Jan 3 '12 at 19:06

Some thing not to look at are the disk sizes of tables and indexes as they may be different in the restored database (I would expect smaller in the restored version, but it depends on a lot of things)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.