Difference in Backup File Size

Question

I'm using Ola Hallengren's backup script. I've added a network share location on the sqlcmd so that when the full backup job runs, the backup file will be saved in two different locations: 1 on the Z drive of the VM, and another one on the network share location. Here’s what the sqlcmd looks like:(please note that I've changed the file path of the network share...)

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\Backups, \\, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" –b

After the job runs successfully, I now see that the backup file (.bak) is saved on both places. However, I've observed that there's a difference in backup file size between the one saved on the Z: drive and the one saved on the Network share.

This is the same case for the rest of the backup files (the one being saved on the network share are larger size). My thinking is that it should be the same size.

Any ideas why the size difference between the two backup files?

Thanks for the help.

Answer 1

I don't think you're doing what you want to do. It looks like you're striping your backups across two paths, which means your backup is evenly split between the two locations.

I suspect that your actual backup size is not 3.5MB, but rather 7MB. The difference in size is most likely due to the local storage being quicker.

Per Ola Hallengren's documentation:

If you specify multiple directories, then the backup files are striped evenly across the directories.

I would strongly suggest modifying this script to only write to one path, and then use an automated method to copy this backup set to another location.