Select Page

How to Automate the Database Restore Process

Author: JP Chen | | November 5, 2015

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:

-- Automating the Database Restores by Generating the Restore TSQL Statements
-- ****************************************************************************
-- Copyright © 2015 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties. 
-- ****************************************************************************

WITH MostRecentBackup_CTE (DatabaseName, MostRecentBackup)
select bs.database_name, MAX(bs.backup_start_date) as MostRecentBackup
from msdb.dbo.backupset as bs
where bs.type = 'D' -- full backups
group by bs.database_name

,bs.database_name as DatabaseName
,bmf.physical_device_name as BackupPath
,bs.backup_start_date as BackupStartDate
,bs.backup_finish_date as BackupFinishDate 
	WHEN bmf.physical_device_name LIKE '%_1.bak' THEN 'RESTORE DATABASE ' +bs.database_name+ ' FROM DISK = ''' + bmf.physical_device_name + ''','
	WHEN bmf.physical_device_name LIKE '%_2.bak' THEN 'DISK = ''' + bmf.physical_device_name + ''','
	WHEN bmf.physical_device_name LIKE '%_3.bak' THEN 'DISK = ''' + bmf.physical_device_name + ''','
	WHEN bmf.physical_device_name LIKE '%_4.bak' THEN 'DISK = ''' + bmf.physical_device_name + ''' WITH STATS = 10, REPLACE, NORECOVERY'
FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf
	ON bs.media_set_id = bmf.media_set_id JOIN MostRecentBackup_CTE as MRB
		ON bs.database_name = MRB.DatabaseName AND bs.backup_start_date = MRB.MostRecentBackup
WHERE bs.type = 'D' -- D = Full, I = Differential, L = Log, F = File or filegroup

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.


How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

MongoDB Best Practices: Design, Deployment & More

This post provides a rundown of best practices to use when running MongoDB.

Esayas Aloto | February 28, 2017

Shortcut to Fixing a Corrupted Oracle Inventory

Oracle Central Inventory files are essential to installing and patching software. The process for fixing a corrupted file can be found here.

Chad Cleveland | February 23, 2016

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.