mssqltips logo

Striping SQL Server Database Backups

By:   |   Updated: 2010-02-12   |   Comments (8)   |   Related: More > 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.


Last Updated: 2010-02-12


get scripts

next tip button



About the author
MSSQLTips author Thomas LaRock 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, June 08, 2016 - 4:25:06 PM - Greg Robidoux Back To Top

Hi Jeremy,

The benefit of striping is to increase IO or network throughput.

I am not sure there is a way to figure out the best number without testing the backup with a different number of files.

You can also get faster IO throughput by writing each file to different disks.

Another approach to creating faster backups is using backup compression.

-Greg

 


Wednesday, June 08, 2016 - 3:52:59 PM - Jeremy Cuttrell Back To Top

 How do I determine the maximum number of stripes that my server can handle?

 


Tuesday, October 01, 2013 - 9:07:24 AM - Rahul Back To Top

Please find the Whitepaper here. It appears to have been moved.


Tuesday, December 18, 2012 - 6:30:01 AM - Wilson R.Gomes Back To Top

Excelente, me ajudou e muito para as tarefas diárias que preciso de velocidade.

parabéns


Tuesday, January 18, 2011 - 3:58:00 PM - ignatz Back To Top

I had a 200GB database that took 4 hours to backup to a network share as a single file.  This server had 4 local drives, each with a tempdb data file but with enough free space to hold the striped backup.  The striped backup to the four local drives took only 7 minutes.  This was followed by two hours of copying the files over the network, but there was no lingering backup process slowing down SQL Server.


Wednesday, August 04, 2010 - 8:36:03 AM - admin Back To Top
You are correct.  The backups should be written to a different drive other than the drive where the database files reside.
This example was done on a local machine (not a server), so it does not point out that fact.
Thanks for pointing out that best practice.

Wednesday, August 04, 2010 - 8:13:53 AM - Joris Back To Top
Helpfull
But! Taking a backup to the 'Program Files' -folder does not match the mantra 'never backup to the same location as the original'.  And being an example specific oriented to large databases .... getting your computer blocked while your operating system-drive is full.

Wednesday, February 17, 2010 - 5:39:29 AM - --cranfield Back To Top

I've only ever seen a max 6% improvement in backup time by striping backups on the same disk. I think this is due to multithreading of the BACKUP when multiple files are used. There is definitely a linear benefit to stripping your backup files across independant disk arrays.  The CAT paper you link to refers to striping backups across the network. They do some funky stuff with multiple NICs, jumbo frames, compression etc. This is an extreme example but is interesting none the less especially when budget doesn't seem to be an issue.

Striping your backups in itself will not speed them up but utilising the striping feature of SQL along with custom designed hardware solution can speed them up especially if you are backing up across a network.

cheers



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools