SQL Server database restore – preparations and tests that we must perform before starting the restore

Goal

To obtain information about the target database files , to check the backup file that we have to use, to estimate the space required and to have a list with steps done and a list with steps to do

My way of achieving it

Using linked server and xp_cmdshell. The restore operation of a SQL Server database is something that requires most of the time careful attention especially when it comes to big databases when the restore operations is time consuming and we would like to have all in place before the restore. Not only that requires our full attention, the restore requires also preparation which is in many cases time consuming and prone to errors and mistakes or something that usually I forget to check. Because of this I decided to create a script that will check some of the things required for a potentially successful restore. The script performs the following verifications:
– checks if the bak file is accessible from the target instance
– checks the drive information of the target instance for space available for the restore
– checks if target instance is older than source instance since backups from newer versions of SQL cannot be restored on old versions
– checks the full bak file for information about the size of the files that will be restored
– check if target database exists on target instance
– check if we have space for individual database files restores and if not provides some alternative locations for relocation
– generate the restore command, is very basic one that needs to be changed in case target database doesn’t exist on target instance

Before using the script we must modify the @sourceinstance, @targetinstance, @sourcedb and @targetdb with the right information.

Th output of the script is below

restoreprerequistes

The script can be downloaded from here

Leave a Reply

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