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.
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.
