I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three destinations. During the testing I noticed that whilst the backup is running each backup file is about 1.2 GB in size, but once the backup process has completed the files shrink to less than 600 MB.
I'm interested in knowing:
- why the files are bigger during the backup process and then shrink?
- whether there is a way to prevent this,
- how big the files are likely to be when I back up that 1.3 TB database - if the files are again going to be double their final size then I may have to ask for some more disk space!
Here's the backup statement that I'm using:
BACKUP DATABASE @DatabaseName TO DISK = @backupMedia1, DISK = @backupMedia2, DISK = @backupMedia3 WITH COMPRESSION, RETAINDAYS = 0, NOFORMAT, INIT, NOSKIP, NAME = @DatabaseName