How to Restore Your Backups from Striped Backup Files

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

Script: RestoreTSQLScriptsSingleOrStripedBackupFiles.sql

Output:

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

Script: RestoringAdventureWorks2012.sql

Output:

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:

1. View the backup files using the RESTORE FILELISTONLY command.
2. Make sure the destination server has enough disk space.
3. Create the folders to store the data and log files for the database.
4. Restore the database with the MOVE commands.
5. 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'

Script: RestoreFileListOnly.sql

Output:

Screen-shot: RestoreFileListOnly

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

Script: RestoringAdventureWorks2012_NEW.sql

Output:

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:

1. 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.
2. 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.
3. 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.
4. 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.