Striping SQL Server Database Backups

By: Thomas LaRock   |   Updated: 2010-02-12   |   Comments (8)   |   Related: > Backup

Problem

As databases get larger, the time for database backups to complete grows as well. Unfortunately, the time window for your backups may not increase. It could very well be the case that you reach a point where your backups will not finish in a specified amount of time.

Solution

An often overlooked solution is to stripe your backups across multiple files. Striping your backups has the potential to dramatically shorten the time it takes for your backups to complete.

You can stripe your backups to different disks but bear in mind that should you suffer a disk failure then your backup will become invalid unless all of the backup files that were created are available to be restored. You can see a benefit in striping across multiple files to the same disk depending upon your RAID configuration and/or the number of available CPU's. And if you are doing your backup across the network then you can also factor in the number of NIC's you have as well. For more details on how to optimize your database backups please see the whitepaper published by the SQL CAT team last year here.

You can stripe a backup using either SQL Server Management Studio or straight T-SQL. In SSMS, simply right-click on the name of the database and then select 'Tasks', followed by 'Back Up...' as shown here:

That action will bring up the Backup Database window. You can click on the 'Add...' button and enter in the file details. In the example below, I am going to stripe my backup across four files, but you could do as many as you want.

You should see the backup striped as shown here:

If you prefer to use only T-SQL, the equivalent BACKUP DATABASE command is as follows:

BACKUP DATABASE [AdventureWorks] TO  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks1.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks4.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks2.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks3.bak' 
WITH NOFORMAT, 
NOINIT,  
NAME = N'AdventureWorks-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

You will need to perform some testing to determine the optimal number of files you want to stripe your database backups onto. The SQL CAT whitepaper gives a chart to help you estimate, but in the end you will want to perform testing in your own environment with different scenarios.

While being able to stripe your backups can be a good thing, having the ability to restore from those same striped backups is even better. After all, as a database administrator your primary function is to have the ability to recover in the event of a disaster.

Restoring from multiple files can be done from either SSMS or with straight T-SQL. To perform the restore from SSMS, simply navigate to the database restore task as follows:

Select 'From device' radio button and then the ellipses at the far right. You should see:

Click 'Add...', then navigate to and select all four files, then click 'OK':

Next you should see:

Select the restore point (in this case, the only checkbox available) and then click 'OK'. Your restore will be underway. The equivalent T-SQL for this restore is as follows:

RESTORE DATABASE [AdventureWorks] FROM  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks1.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks4.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks2.bak',  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TURBACONDUCKEN\MSSQL\Backup\AdWks3.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Next Steps

• Review the SQL CAT whitepaper and determine if striping your backups will help reduce the time it takes for your database backups to complete.

About the author

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips