window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-6826746-1', {'use_amp_client_id': true}); (function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= '//'+i+dl;f.parentNode.insertBefore(j,f); })(window,document,'script','dataLayer','GTM-59HNNL'); // var iframe = document.getElementById('iframe-pardot-form'); // iframe.src = iframe.src +; var iframe = document.getElementById('iframe-pardot-form'); function setIframe() { if (iframe) { iframe.src = iframe.src +; } else { setTimeout(function() { iframe = document.getElementById('iframe-pardot-form'); setIframe(); }, 100) } } setIframe();
Select Page

How to Restore Your Backups from Striped Backup Files

Author: JP Chen | | June 29, 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.

-- 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.

-- ****************************************************************************


,bs.database_name as DatabaseName

--,bmf.physical_device_name as BackupPath

,bs.backup_start_date as BackupStartDate

,bs.backup_finish_date as BackupFinishDate



       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'


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


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


ALTER DATABASE AdventureWorks2012



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


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


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


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


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'


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',


Script: RestoringAdventureWorks2012_NEW.sql


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.
    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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Oracle Database Extended Support Deadlines: What You Need to Know

If you’re confused about Oracle’s extended support deadlines, you are not alone. Here’s an overview of what’s in store for 11g through 19c.

Steve Thompson | January 19, 2021

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.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.