How to Restore Your Backups from Striped Backup Files

By | In Database Administration, SQL Server | June 29th, 2016

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.

Script: RestoreTSQLScriptsSingleOrStripedBackupFiles.sql

Output:

Restore Scripts Generated

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.

Script: RestoringAdventureWorks2012.sql

Output:

Restoring AdventureWorks 2012

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.

Script: RestoreFileListOnly.sql

Output:

Restore File List Only

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.

DB Folders

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.

Script: RestoringAdventureWorks2012_NEW.sql

Output:

Restoring AdventureWorks2012 New Database

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.

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).