Backup to multiple files

DarrenGuyDarrenGuy Posts: 9
edited September 12, 2011 6:10AM in SQL Backup Previous Versions
I have the following scenario.

I am backup up a database to disk, and then with the resulting file I am sending the backup file to amazon's S3 storage.
However, as the backup files are large, I am using 7zip to create smaller chunks of the backup file and it is these files that I am sending to S3 so that if a file transfer fails, I dont mind it failing on a 1GB file and retrying, but annoyed if the upload fails on a 200GB file.

What I am looking to determine if it is at all possible that when performing a backup, when the backup file reaches a certain file size, eg 2GB, a seperate file is then created.

Currently I am backing up to file name
Nvq_20110805_013500.sqb.
What I would have to achieve is something similar to:

Nvq_20110805_013500.sqb
Nvq_20110805_013500_.001.sqb
Nvq_20110805_013500_.002.sqb
etc

I have looked in BOL and they talk about doing striped backups, but this does not allow you to specify multiple breaking up the backup by file size.

I did find this forum post from 2009, but was wondering if the situation since then has changed

http://www.red-gate.com/messageboard/vi ... 05387c5390

I am using SQL Backup 6.5

Thanks in advance

Comments

  • Darren,

    You can use the FILECOUNT = parameter to split the backup into multiple files. I don't know about any size cutoff method but if your current backup is say 5Gb then FILECOUNT = 3 will produce 3 1.7Gb outputs.

    HTH

    Chris
    English DBA living in CANADA
  • peteypetey Posts: 2,358 New member
    , but this does not allow you to specify multiple breaking up the backup by file size.

    SQL Backup does not allow you to do this i.e. split the backup set into smaller files of a maximum size. As Chris suggested, you can use the FILECOUNT parameter to split your backup set into multiple files. Or you could use multiple DISK parameters if you want to use a different naming convention.

    If all the files are written to the same disk, the files would be of roughly equal sizes. If writing to multiple disks/storage devices, the files on the slower disks/storage devices would be smaller than the files on the faster disks/storage devices.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks for the replies.

    I am already using multiple DISK parameters. But this does not answer my original question about using sql backup to limit file sizes.

    I guess that this is something that is still not possible.
  • peteypetey Posts: 2,358 New member
    DarrenGuy wrote:
    I am already using multiple DISK parameters. But this does not answer my original question about using sql backup to limit file sizes.

    I guess that this is something that is still not possible.
    No, it is not possible to limit the backup file sizes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.