Checking Databases after Backup / Restore

Checking Databases after Backup / Restore

When Moving a Database between Servers – e.g. migrating to a new Production Server, how can you be absolutely sure that the Backup that you restored is the correct one?

The nature of the DBA role dictates a certain amount of OCD. Most people work in a methodical manner – using a specific Folder for backup, checking the Name, Date and Time of the Backup file before copying it over to another Server. Using Tools such as Ola Hallengren’s Maintenance Solution help by creating Filenames with the Database name, plus Date / Time of Backup. Files are generally copied to a specific Folder for restore. This is good, but is it sufficient?

The importance of restoring the correct backup is paramount, but so is being able to prove that you have restored the correct backup. This proof may be required for audit purposes, but it’s more important for your own sanity.

How about asking your Primary User to check the Data? Yes, they can do this by looking for something that they have changed just before the Database was backed up. This may give them confidence that their data has been moved successfully. You can be sure that any other Changes made on the source system before this would have been applied, but what about any system data changes that might have happened afterwards?

I think that we need to be a bit more sure. Backup files can hold more than one Backup. Stuff happens.

SQL Server holds a history of Backups that are run, together with Restores that it has performed. This information includes Filenames, allowing a simple check to be made that a Backup on one Server correlates to a Restore on another.

The following Query can be run on the Source Server to view Backups that have been taken.

 

The following Query can be run on the Destination Server to view Backups that have been restored.

 

Armed with the results from these two queries, you can confidently proceed, knowing that you have Restored the correct Backup.

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close