SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

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'

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


Posted by saurabhinvertis on 4 November 2011

Could you please tell me what is the meaning of STATS= 10

in the database back up command.

Leave a Comment

Please register or log in to leave a comment.