In a previous blog, How to Stripe Your Backups into Multiple Files?, we had explored the technique to stripe the database backups into multiple backup files to speed up the backup duration. In this blog, we will look into how we can generate the TSQL restore scripts automatically and then execute them to perform the restores.
Just as you can query the msdb.dbo.backupset and the msdb.dbo.backupmediafamily system tables to retrieve the backup histories, you can also generate TSQL restore scripts using these two system tables. In addition to saving you time, it will minimize the human errors you will made if you had to manually type out the restore commands. This automation technique will be immensely valuable for database migrations or disaster recovery projects.
Previously, as a practice run, we had executed the database backups for the AdventureWorks2012 and AdventureWorksDW2012 databases using the single backup file and then the multi-file backup technique. The following script will help generate the TSQL restore scripts to restore the databases from single-file backups and also from multi-file backups.
-- Generate TSQL script to restore the single and multi-file backups -- **************************************************************************** -- Copyright © 2016 by JP Chen of DatAvail Corporation -- This script is free for non-commercial purposes with no warranties. -- **************************************************************************** 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 SUBSTRING(bmf.physical_device_name, LEN(REVERSE(bmf.physical_device_name)) - 5, 1) <> '_' THEN 'RESTORE DATABASE ' +bs.database_name+ ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH STATS = 10, REPLACE, NORECOVERY' 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, RECOVERY' END AS RestoreTSQL FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id WHERE bs.type = 'D' -- D = Full, I = Differential, L = Log, F = File or filegroup AND bs.database_name IN('AdventureWorks2012','AdventureWorksDW2012') -- specify your databases here ORDER BY BackupFinishDate DESC
Screen-shot: Restore Scripts Generated
You can then save the RestoreTSQL column for the database restores you need. In the following example, we will simulate a disaster recovery scenario by restoring the AdventureWorks2012 database on the same SQL Server instance replacing the existing one. We will first set the AdventureWorks2012 database offline to disconnect all users and then perform the restore.
-- Restoring the AdventureWorks2012 database USE master GO ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE AdventureWorks2012 FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak' WITH STATS = 10, REPLACE, RECOVERY
Screen-shot: Restoring AdventureWorks2012
What if you need to migrate the database over to a separate SQL Server instance? There are a few additional steps you need to work on:
- View the backup files using the RESTORE FILELISTONLY command.
- Make sure the destination server has enough disk space.
- Create the folders to store the data and log files for the database.
- Restore the database with the MOVE commands.
- Resolve the orphaned users if any.
We will now make believe we are in a database migration scenario. We will restore to create a new database, AdventureWorks2012_NEW, on the same instance using the backup files for the AdventureWorks2012 database. To view the data and log files in the backup files, we will use the RESTORE FILELISTONLY command.
-- View the data and log files in the backup files RESTORE FILELISTONLY FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak'
Next, we need to determine the folders to store the data and log files for the database to be created through the restore process. In this case, we will use the E:\SQLData for the data files and the L:\SQLLog for the log file.
Screen-shot: DB folders
We will now create the AdventureWorks2012_NEW database through restore. This is the most common and effective technique for database migrations.
-- Creating the AdventureWorks2012_NEW database through restore RESTORE DATABASE AdventureWorks2012_NEW FROM DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_1.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_2.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_3.bak', DISK = 'E:\SQLBackup\AdventureWorks2012_20160408111758_4.bak' WITH MOVE 'AdventureWorks2012_Data' TO 'E:\SQLData\AdventureWorks2012_NEW_Data.mdf', MOVE 'AdventureWork_Data2' TO 'E:\SQLData\AdventureWork_NEW_Data2.ndf', MOVE 'AdventureWorks2012_Log' TO 'L:\SQLLog\AdventureWorks2012_NEW_log.ldf', STATS = 10, RECOVERY
Screen-shot: Restoring the AdventureWorks2012_NEW database for database migration
As part of the database migration process, the next step will require you to resolve the orphaned users if any. We will explore the steps on how to do that in a future blog.
Datavail Script: Terms & Conditions
By using this SQL Server script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:
- CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as set-forth herein.
- LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
- USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.
- LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.
You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.
5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?