5

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?

|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
3

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: https://serverfault.com/questions/187869/should-i-run-dbcc-checkdb-before-full-backups-or-after

|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
2

As someone already mentioned in their reply running DBCC CHECKDB after a restore can be a good practice. This way as a backup is a copy of a db you will verify both backups and restores without any impact on a production DB.

You can check this article that describs how to run and/or automate a DBCC CHECKDB after a database restore by automating DBCC CHECKDB after a database restore by using a SQL Server Agent job, Maintenance Plans, and ApexSQL Manage.

How to automate DBCC CHECKDB after a database restore

Disclaimer: I work for ApexSQL

|improve this answer
0

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)

|improve this answer
0

I know this is a little bit late. but you seem to have a similar process like ours. where you need to pull backup from live and restore to test database.

Please check the below script that automates the whole process. at the end of the script published you will find a xml configuration data. copy it and place it in a separate .xml file.

http://social.technet.microsoft.com/wiki/contents/articles/28253.restore-sql-database-to-an-ag-with-powershell.aspx

|improve this answer
  • 1
    Link-only answers should at least include the most important parts of the source. Links become invalid quite often. – tombom Nov 4 '14 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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