If you have to backup large SQL Server database with the help of SqlBak, here you will find some recommendations that can speed up the backup process.
1. Free space on your local drive
If you need to backup large database, first of all, you need to make sure that you have enough free space on your local drive. Even if you store your backups somewhere in the cloud, you still need to have enough free space on your local drive to keep the temporary files.
To specify the temporary files folder go to your SqlBak backup job settings and find the “Backup options” menu. Then specify a folder on your local drive at the “Temp files folder” box. If your local drive is divided into several partitions we recommend you use the biggest one.
2. Use 7 zip compression
To reduce a backup file size you can compress backups using 7-zip compression. This option is available on the “Compression” menu, just select “.7z” from the drop-down list at the “Archive format” section. Also, you can speed up the compression procedure at the “7-zip command-line switches” box, just add the following command-line option:
-m0=LZMA2 -mmt=on
Once you decided to apply this command you have to remember that it loads the CPU higher.
3. Backup one DB, send, repeat
If you backup many databases in one backup job we also recommend you to use the “Backup one DB, send, repeat (as opposite to backup all, then send all)” option, which is located at the “Backup options” menu. If you checked that option, then SqlBak will backup each database one by one in the following way:
- backup
- compress
- send on destinations
- remove it from the temporary folder
By default this option is unchecked and SqlBak backup and compress all databases first, send on destinations and only then remove them from the temporary folder.
SUGGESTION: PLEASE add feature to SKIP temp file creation. We backup large (200+GB) databases to an iSCSI NAS. The backup goes to “H:\SQLTEMP”, then gets copied from “H:\SQLTEMP” to the designated storage folder “H:\SQLBACKUP”. This is a waste of disk traffic, network traffic, time, etc…
If Temp and Storage folders are on same drive, they can be MOVED instead of copied.
OR…if you provide an advanced option to not use Temp file/folder, then backups can be written directly to final storage location.
PLEASE CONSIDER OUR SUGGESTION!!
Thanks
Hi Nick,
Thanks for the feature request. Will it works for you if we implement this behavior:
Hi
i have applied the the recommended command line paramters -m0=LZMA2 -mmt=on
to the 7 zip compression you have suggested and the backup failed. i got the following error:
System ERROR:
The parameter is incorrect.
Hi Majedm,
Could you please make sure that you choose .7z as the archive format at the “Compression” section.
Thank you.