Striped backups in SQL Server

Some DBA's don't realize that SQL server can perform a database backup that breaks up the files - stripes them - in a single command.  SQL can stripe the backup into an unlimited number of files, creating a multi-file backup set.



I got a request from a client to break a database backup into chunks of 250mb.  This situation was perfect for the striped backup capability of SQL Server.  No need to use an archiving tool to zip up and break up a set of files.  With SQL Server Enterprise edition 2008 or 2008 R2, we can even do the work of compression.

After figuring out how large the previous night's compressed backup was, I knew how many files do use.  This is one needed feature - tell SQL Server what size files you want, it determines the number of files.

backup
database userdatabase to
  disk = 'h:\Backups\userdatabase_full__201108300518_1.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_2.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_3.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_4.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_5.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_6.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_7.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_8.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_9.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_10.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_11.bak'
, disk = 'h:\Backups\userdatabase_full__201108300518_12.bak'
WITH
COPY_ONLY, COMPRESSION, STATS= 10

This same task can ofcourse be accomplished in SSMS by adding more backup file locations.