Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Database User and Programming Tips

Posted June 1, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

How to Generate a Restore Script

By Greg Larsen

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.  

See all articles by Greg Larsen



Database User and Programming Tips Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.