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:
https://gallery.technet.microsoft.com/scriptcenter/How-to-generate-database-5fc697f5
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.
