Backups - Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately, they only work in the context of executing agent jobs.

We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive.

We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the time running the backup step itself is slower. I am finding that even when the backup process time itself is slightly longer, I perfer this setup because we don’t need to execute batch files stored on the operating system (with its related security worries), nor do we have to worry about allowing memory space for the copy process to run in outside of sql server. (Along with everything else that uses up that memory space.)

Here is a sample of the modified backup commands we’re using— this one is from an 11GB SQL 2008 database where I’m using five files for the compressed backup. The total backup size across the file files is 5.5GB and it completes across the network (within a datacenter) in ~10 minutes.

BACKUP DATABASE MyDB
	TO DISK='\\BackupServer\MyBackupDirectoryShare\MyDB\_db\_$(ESCAPE\_SQUOTE(DATE))$(ESCAPE\_SQUOTE(TIME))\_1of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB\_db\_$(ESCAPE\_SQUOTE(DATE))$(ESCAPE\_SQUOTE(TIME))\_2of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB\_db\_$(ESCAPE\_SQUOTE(DATE))$(ESCAPE\_SQUOTE(TIME))\_3of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB\_db\_$(ESCAPE\_SQUOTE(DATE))$(ESCAPE\_SQUOTE(TIME))\_4of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB\_db\_$(ESCAPE\_SQUOTE(DATE))$(ESCAPE\_SQUOTE(TIME))\_5of5.DMP'
WITH COMPRESSION