Backup to multiple files
DarrenGuy
Posts: 9
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
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
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8