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. Identify the databases to be migrated on the source server.
- 2. Create the “shell” databases in the destination server.
- 3. Develop the TSQL restore statements to perform the restores.
- 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.
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) AS ( 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 ) SELECT SERVERPROPERTY('SERVERNAME') as InstanceName ,bs.database_name as DatabaseName ,bmf.physical_device_name as BackupPath ,bs.backup_start_date as BackupStartDate ,bs.backup_finish_date as BackupFinishDate , CASE 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' END AS RestoreTSQL 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!
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.
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.
Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.