Restore database using multiple .bak files (SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command)

USE [master]
RESTORE DATABASE [HES] FROM
DISK = N'C:\Naveen\HES\HES_00.bak',
 DISK = N'C:\Naveen\HES\HES_01.bak',
 DISK = N'C:\Naveen\HES\HES_02.bak',
 DISK = N'C:\Naveen\HES\HES_03.bak',
 DISK = N'C:\Naveen\HES\HES_04.bak'
 WITH  FILE = 1,  MOVE N'HES' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HES.MDF',  MOVE N'HES_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HES_Log.LDF',  NOUNLOAD,  STATS = 5

GO

http://sqlserverzest.com/2013/09/03/sql-server-how-to-perform-striped-database-backup-restore-using-t-sql-command/

SQL Server – How to Perform Striped Database Backup & Restore Using T-SQL Command

September 3, 2013 by Suresh Raavi

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).
As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

By Striping a backup we can:

• Increase backup throughput and reduce the backup time window
• Allow backups & restores to be written or to be read from all devices in parallel
• Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.
T-SQL command for Striping a database backup
Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can  direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup 

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it
T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup
Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup
Results:

Backup Type	Time to Backup	Data Transfer Rate
Normal Backup (1 File)	537.6 Seconds	111.9 MB/Sec
Striped Backup (4 Files)	201.0 Seconds	299.3 MB/Sec

Screenshots:


However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.