This is one of those forehead slapping, "duh" shouting, tips.  I can't wait to test it out.  So does the reverse apply to restores?  If I span my nightly backups accross 4 files and that cuts the time that the backup takes to finish by, say, 50%, will the restore also take less time? 

The problem with the restore is that you still need to write the data, so you won't be saving any time on the write.  It is possible that the reads will be faster if you are reading from multiple disks and therefore getting as much read IO throughput as possible.

This would also be the case for writing the database files.  If you are writing to multiple disks and can get more IO throughput your restores will run faster.

A couple of corrections. First of all, you get 1 backup thread per LUN or mountpoint. So you do NOT get multiple threads simply by backing up to multiple files on the same drive. They must be segmented to different drives to get the benefit of multiple threads.

The second correction is to the comment reply above. Yes, the same absolutely does apply to restores as well. Just like backups, you get 1 thread per LUN or mount point for the restore as well. This gives you the ability to read in multiple files on multiple threads. You may end up being bottlenecked by the write speed if everything is going to the same drive. You can improve this even more by using multiple data files on multiple dedicated LUNs.

Great tip -- but I just have to know how SQL divides up the data and decides what to write in which file. Is it simply random blocks/extents within the database, or does it divide it up by table/object somehow? I would also presume that if you lost any one of the files you backed up to, the entire backup would be unusable -- is that correct?

Hi Rob, not sure how it determines what goes in each file.

In order to do a restore you would need all of the files.  You can not just restore one of the files, so yes without one of the files the backup would be unusable.

- Actually multiple files to 1 drive can improve speed for situations where the disk is not the limiting factor. For larger db's we checked the time needed for back-up's and multiple files where faster. Think about compression/encryption type of back-up where the load on the cpu is limiting. Multiple threads -> multiple cpu's -> higher speed.

- About your blog: an interesting use of this technique i saw in a scenario where a TB+ size db had to be backupped over a wan connection. They made like 10 networkdrives and each networkdrive pointed to a specific ip number with it's own subnet connecting to a specific connection to the other side.

You don't get multiple threads if it is going to a single drive. You get 1 backup thread per LUN or mountpoint. Also, how often is CPU the limiting factor when performing a backup. Even when using compression and encryption, it is almost always disk that is the bottleneck.

Hi

Nice post. thx.

i put that in place on a 2008 sql server (also playing with other parameters explain there : http://www.mssqltips.com/sqlservertip/2539/options-to-improve-sql-server-backup-performance/) 

I got one question : as someone been able to delete backup inside backupset based on expiration time ?

I've set my backup to expire after one day but which ever the settings i used it always append the new backup to the backup set, so I got files growing and growing...

Any idea ?

thx

If you want to reuse the same file over and over again and don't want to append backups to the same file you will need to use the INIT option to initialize the backup file before the backup is created.

Thx for ur comment.

We can use the init if we only want to keep one backup. That works.

What about if we want to keep 2 days rolling backup. How to implement the Init option in that way ?

thx a lot

Based on the info from Books Online if you use an expiration date for the backups the INIT only clears the backups where the backup has expired.

This is from Books Online:

INIT
Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit. By default, BACKUP checks for the following conditions and does not overwrite the backup media if either condition exists:

- Any backup set has not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
- The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME option, earlier in this section.

To override these checks, use the SKIP option.

Tripp here again (orginal reply from 2008).  Just wanted to pass on that I did end up developing and deploying a "backup to multiple files" solution for a couple of 100+ GB databases.  In my case I'm backing up directly to a DataDomain device (in the same datacenter on the same switch), bypassing the usual Tivoli "middle-man" due to licensing "constraints", so I couldn't just stripe the FULL backups across as many unique drives as I wanted to.  I ended up striping the backups across two files on the same DataDomain share, and using a BUFFERCOUNT of 100, cut the individual databases' backup times from 2 hours down to 15-20 minutes. 

This did not change the recovery time one bit.  Refreshes from those production backups to test and dev still take 30-45 minutes. 

Thanks for the article, and for maintaining the comments all this time. 

Thx for your answer Greg.

So I think I have aprob with that or something I don't understand :Here is the backup command I'm running :

BACKUP DATABASE [edeal] TO  DISK = N'H:\SQL_Backup\Edeal_Bak_3.bak',  

DISK = N'H:\SQL_Backup\Edeal_Bak_1.bak',  

DISK = N'H:\SQL_Backup\Edeal_Bak_2.bak',

DISK = N'H:\SQL_Backup\Edeal_Bak_4.bak' 

WITH  RETAINDAYS = 1, NAME = N'edeal-Full Database Backup', NOFORMAT, INIT,  buffercount=47, maxtransfersize=4194304, BLOCKSIZE = 65536, NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

And after 2 days of running job I got this error :

Executed as user: xxxxxservice. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  The medium on device 'H:\SQL_Backup\Edeal_Bak_3.bak' expires on Feb 22 2012  8:48:22:000AM and cannot be overwritten. [SQLSTATE 42000] (Error 4030)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Any help appreciate