How to Generate a Restore Script

If you have to restore a database there are multiple ways to accomplish a restore.  But if you wanted to restore a database from a script how might you accomplish that task quickly and accurately?  One method is to write a process that uses the metadata in the msdb database to generate a restore script.

The metadata in the msdb database keeps track of all the backup information associated with a database.  Every time a backup is taken, SQL Server records the backup activity into the SQL Server backup metadata in the msdb database.  Therefore, by reading the metadata in msdb you can identify where the backups reside and then generate the appropriate restore script.  

To build that restore script you could write your own code to generate that restore script.  Or you could use a process written by someone else.  I’m a big fan of cloning what others have done to meet my requirements.  Therefore, in this tip I’m not going to show you how to write your own restore script, but instead I’m going to provide you a hyperlink to a script that someone else developed.

The scripting process I’m going to identify for you, will take the backup metadata for the database you want to restore and will then generate a restore script you can use to restore your database.   You can download the script that generates a restore script here:

As long as msdb still contains backup metadata information for your database the downloaded script from the above location will be able to generate a restore script.  Next time you want to generate a restore script from the backup metadata stored in the msdb database you might want to check out this free backup script generation process.  

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles