How to Restore Multiple Backup Files in SQL Server 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 by handling multiple backup sets. 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. It is crucial to select all the backup files associated with a specific database during the restoration procedure to ensure a complete and successful recovery.

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:SQLBackupAdventureWorks2012_20160408111758_1.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_2.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_3.bak',

DISK = 'E:SQLBackupAdventureWorks2012_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:SQLBackupAdventureWorks2012_20160408111758_1.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_2.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_3.bak',

DISK = 'E:SQLBackupAdventureWorks2012_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:SQLBackupAdventureWorks2012_20160408111758_1.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_2.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_3.bak',

DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_4.bak'

WITH

MOVE 'AdventureWorks2012_Data' TO 'E:SQLDataAdventureWorks2012_NEW_Data.mdf',

MOVE 'AdventureWork_Data2' TO 'E:SQLDataAdventureWork_NEW_Data2.ndf',

MOVE 'AdventureWorks2012_Log' TO 'L:SQLLogAdventureWorks2012_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.

Introduction to Restoring Databases

Restoring databases is a critical task for database administrators, especially when dealing with Microsoft SQL Server. This process involves recovering data from a backup or a previous version of a database. SQL Server Management Studio (SSMS) is a primary tool for working with SQL Server, and it provides a graphical interface for restoring databases. In this section, we will introduce the concept of restoring databases, including the use of backup files, transaction log backups, and differential backups.

Restoring a database can be necessary for various reasons, such as recovering from data corruption, migrating data to a new server, or setting up a test environment. The process typically involves using backup files, which are copies of the database at a specific point in time. These backups can be full backups, which include the entire database, or differential backups, which only include changes made since the last full backup. Additionally, transaction log backups capture all the transactions that have occurred since the last backup, allowing for point-in-time recovery.

Using SSMS, you can easily restore databases by selecting the appropriate backup files and specifying the desired recovery options. This tool simplifies the restoration process, making it accessible even for those who may not be as familiar with T-SQL commands. However, understanding the underlying concepts and commands can provide greater flexibility and control over the restoration process.

Understanding BAK Files

BAK files are physical copies of the data from the database files (.mdf and .ldf) in Microsoft SQL Server. These files are designed to restore databases to the state they were in at the time the backup was created. BAK files can be restored using T-SQL commands, graphical utilities like SSMS, or from the command line. It is essential to understand the structure and content of BAK files to perform successful database restorations.

A BAK file contains all the necessary information to recreate the database, including the data, schema, and transaction logs. When you create a backup in SQL Server, the resulting file typically has a .bak extension. This file can be stored on disk, tape, or other storage media. To restore a database from a BAK file, you need to specify the file’s location and use the appropriate T-SQL commands or SSMS options.

Understanding BAK files is crucial for database administrators, as it allows them to effectively manage and restore databases. By knowing how to create, store, and use BAK files, you can ensure that your data is protected and can be recovered in case of an emergency.

Preparing for Restore of SQL Database

Before restoring a SQL database, several steps must be taken to ensure a successful restoration process. This includes verifying the backup files, checking the database name, and ensuring that the SQL Server user has access to the directory where the BAK file is located. Additionally, if restoring a database to a new location, the logical names of the files must be updated to reflect the new directory.

First, verify that the backup files are complete and not corrupted. You can use the RESTORE VERIFYONLY command in T-SQL to check the integrity of the backup files. Next, ensure that the database name specified in the restore command matches the name of the database you intend to restore. If you are restoring to a new database, make sure to use a unique name to avoid conflicts.

Access permissions are also critical. The SQL Server user performing the restore must have read access to the directory where the BAK file is stored. If the BAK file is located on a network share, ensure that the SQL Server service account has the necessary permissions to access the share.

Finally, if you are restoring the database to a new location, update the logical names of the files to reflect the new directory structure. This can be done using the MOVE option in the RESTORE DATABASE command. By following these preparatory steps, you can ensure a smooth and successful database restoration process.

Restoring Databases using Multiple Files

Restoring databases from multiple backup files is a common scenario, especially when dealing with large databases. SQL Server Management Studio provides an option to select multiple backup files for restoration. To restore a database from multiple files, the backup files must have the same prefix, and the S3 ARN (Amazon Resource Name) must include the common prefix followed by an asterisk (*).

When dealing with large databases, splitting the backup into multiple files can significantly reduce the time required for both backup and restore operations. This technique, known as striped backups, allows SQL Server to read from multiple files simultaneously, speeding up the process.

To restore a database from multiple files, you can use the RESTORE DATABASE command in T-SQL, specifying each backup file in the sequence they were created. For example:

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_1.bak',
DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_2.bak',
DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_3.bak',
DISK = 'E:SQLBackupAdventureWorks2012_20160408111758_4.bak'
WITH STATS = 10, RECOVERY;

In Amazon RDS for SQL Server, you can use the rds_restore_database stored procedure to restore databases from multiple backup files stored in Amazon S3. This procedure simplifies the process by allowing you to specify the S3 ARN with a common prefix followed by an asterisk (*), indicating that all matching files should be used for the restore operation.

By understanding how to restore databases using multiple files, you can efficiently manage large databases and ensure quick recovery times.

Working with Differential Backups

Differential backups contain only the changes made since the last full backup. To restore a differential backup, the previous full backup must be restored with NORECOVERY, followed by the restoration of the differential backup with RECOVERY. In this section, we will explore the process of working with differential backups, including the use of T-SQL commands and SQL Server Management Studio. We will also discuss the importance of understanding the recovery model and the role of transaction log backups in the restoration process.

Differential backups are an efficient way to reduce the amount of data that needs to be backed up and restored. By only capturing the changes since the last full backup, differential backups can be smaller and faster to create. However, to restore a differential backup, you must first restore the last full backup with the NORECOVERY option, which leaves the database in a restoring state. Then, you can apply the differential backup with the RECOVERY option to bring the database online.

For example:

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'E:SQLBackupAdventureWorks2012_Full.bak'
WITH NORECOVERY;

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'E:SQLBackupAdventureWorks2012_Diff.bak'
WITH RECOVERY;

Understanding the recovery model of your database is crucial when working with differential backups. The recovery model determines how transaction log backups are managed and how point-in-time recovery can be achieved. In the Full recovery model, transaction log backups are required to capture all transactions, allowing for point-in-time recovery. In the Simple recovery model, transaction logs are automatically truncated, and point-in-time recovery is not possible.

By mastering the use of differential backups and understanding the recovery model, you can optimize your backup and restore strategy, ensuring that your databases are protected and can be quickly restored in case of an emergency.