CopyTo not Compressing

jhelmbrecht

I have a scheduled backup created that makes the backup copy local and uses the network copy to place a backup on a share location. The local copy gets compressed, however, the network backup copy is not being compressed. Did i miss something in the configuration or is the network copy not compressed?

Here is the script:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [BackupDb] TO DISK = ''D:\xxx\<AUTO>.sqb'' WITH ERASEFILES_ATSTART = 5b, ERASEFILES_REMOTE = 5b, FILEOPTIONS = 4, MAILTO = ''test@test.com'', PASSWORD = ''<ENCRYPTEDPASSWORD>hidingthis</ENCRYPTEDPASSWORD>'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, COPYTO = ''\\fileserver\share'', KEYSIZE = 256, THREADCOUNT = 23, VERIFY"'

Thanks in advance for any feedback!
JH

Eddie D

Thank you for your post into the forum.

Both the local copy and network copy should be compressed and be the same file size.

SQL Backup compresses the data before it is written to disk.

SQL Backup issues the backup command to SQL Server and creates a Virtual Device Interface (VDI). SQL Server collects the required database backup data and writes the data to the VDI. SQL Backup collects and compresses the backup data, the amount of compression is dependent on the compression level used (and if configured encrypts the data). The compressed data is written to a local disk or backup file path configured (as set by the TO DISK = argument). Once the backup process has completed and the COPYTO argument is in the backup command, the backup file is then copied from the local disk to the network share or the file path configured (as set by the COPYTO = argument) using the Windows Copy API.

You cannot set different compression levels for the Primarily Backup location and the COPYTO location. However you can set different backup file retention periods.

I hope the above answers your question.

Many Thanks
Eddie

jhelmbrecht

Upon further investigation, it looks like it is working correctly. Our db is 50gb in size and compressed at level 4 it gets down to around 15gb. I was worried because it appeared initially that it was writing the full 50gb file to the copy location. All is well! Thanks for your prompt response!