How to backup large databases. Recommendation.

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.

4 thoughts on “How to backup large databases. Recommendation.”

  1. 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

    Reply
    • Hi Nick,

      Thanks for the feature request. Will it works for you if we implement this behavior:

    • 1. Backup a database into backup/temporary folder.
    • 2. If the job has a local folder destination, compress the backup into the folder destination else compress the backup into the temporary folder.
    • 3. Upload the backup file on destinations. Skip the folder destination if the file was saved on it.
    • 4. Delete the compressed backup if it’s on the temporary folder.
    • Reply
  2. 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.

    Reply

Leave a Comment