How to Automate the Database Restore Process

By | In SQL Server | November 05th, 2015

Automate Backup

Automating any repetitive tasks, reducing the chance of error, or cutting hours into minutes isn’t lazy, it’s smart. Imagine you are assigned to work on the critical migration project and need to migrate more than one hundred databases from the source server over to the destination server, would you opt for the GUI for each of the database restores or generate the TSQL scripts for the restores? The latter is the clear choice. In this blog, we will explore on how we can automate the database restore process by generating the required TSQL statements.

One of the most common and effective database migration strategies involves the following critical steps:

  1. 1. Identify the databases to be migrated on the source server.
  2. 2. Create the “shell” databases in the destination server.
  3. 3. Develop the TSQL restore statements to perform the restores.
  4. 4. Execute the TSQL restore statements through a scheduled SQL Server Agent job.

Prior to the migration, the databases to be migrated will be identified as per business requirements. To create the “shell” databases in the destination server, you can script them out from the source server and then update the file locations to be created in the destination server and then get them executed. For example, in SQL Server Management Studio (SSMS) connect to the source instance, right-click on the database name, select “Script Database as” from the pop-up menu, select “CREATE To”, and then select “New Query Editor Window” to generate the script to create the “shell” database in a new window. You can then update the file locations accordingly as per the drives and folders availability in the destination server.

Automate Restore 1

We can find the most recent full backups for each of the databases by querying the msdb.dbo.backupset system table. In this case, we will use a Common Table Express (CTE) to temporary store the result set of the most recent full backups for each of the databases. We will then join the CTE to the msdb.dbo.backupsetand msdb.dbo.backupmediafamily system tablesand by using a searched CASE expression to dynamically generate the restore TSQL statements. Here are the system tables, functions, conversions, and case expressions that you need to know to get started:

  • MSDB.DBO.BACKUPSET: This system table stores the information about the backup operations. It contains critical information such as the backup start date, backup end date, backup size, backup type, and other important backup details.
  • MSDB.DBO.BACKUPMEDIAFAMILY: Contains one row for each media family.One of the most important data column in this system table is the physical_device_name data column. It identifies the physical path of the backup.
  • Common Table Expression (CTE): It is used to store a temporary result set that lasts only for the duration of the query.
  • SERVERPROPERTY (‘SERVERNAME’): Returns property information about the server instance. By passing in ‘servername’ as the parameter, it returns the instance name.
  • MAX (): This function returns the maximum value in the expression. We will use this function to identify the most recent backup for each of the databases.
  • CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the search CASE expression.

The following query will do the magic:

You can then copy the restore TSQL statements on the “RestoreTSQL” column and create a SQL Server Agent job on the destination server to execute the TSQL statements to perform the restores. We are using the NORECOVERY option here as you will need to also take differential and/or transaction log backups and restore them over to the destination server.

Critical note: Make sure the SQL Server Agent account has read access to the folder where the backups are stored!

Automate Restore 2

After the restores are completed, you can confirm the restores by querying the msdb.dbo.restorehistory and the msdb.dbo.restorefile system tables. Some of the additional follow-ups involve creating the required logins, users, resolving the orphaned users, publications or subscriptions, and adding the databases on the destination server to the SQL Server maintenance jobs such as backup, index maintenance, and database integrity check.


Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: and

Leave a Reply

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

1 thought on “How to Automate the Database Restore Process”
  1. Hi JP Chen ,
    Is There any Automation process for restore
    I Want to restore total log files in sqlserver after full ,differential backups
    Thanks & Regards