Split backup into multiple files

R JR J Posts: 12
edited December 15, 2009 12:30PM in SQL Backup Previous Versions
I was wondering how the "Split backup into multiple files" feature works. How does the software determine the size of each file or how many files to split the backup into? Is there a way to set a max size for each file?

Comments

  • Hi RJ,

    An example of a split backup (3 files) command is shown below:
    [EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [test] TO DISK = ''E:\Backups\<AUTO>_1.sqb'', DISK = ''E:\Backups\<AUTO>_2.sqb'', DISK = ''E:\Backups\<AUTO>_3.sqb'' WITH DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4"'
    

    When you choose this option, we open as many virtual device interfaces (VDI) to SQL Server as there are files requested by the user. Each of the datastreams that SQL Server provides to SQL Backup is identical to what you'd find in the native backups files if you gave more than one TO DISK argument in a native backup.
    How does the software determine the size of each file or how many files to split the backup into?
    SQL Server performs the task of how to split the data and then SQL Backup performs encryption/compression on each of the streams independently. The number of files is chosen by the user just by specifying more TO DISK arguments.
    Is there a way to set a max size for each file?
    As the files are written in parallel, you can't have SQL Backup write the files sequentially up to a given max file size.

    Hope this answers your questions,
    Robin Anderson
    Development
    Red-Gate Software
Sign In or Register to comment.