I have a script that runs through all our databases and backs them up.
This looks like:
SET @BackupFile = @dbname + '_full_' + @time_stamp+ '.zbak'
SET @complete_filepath = @filepath_backup + @BackupFile
SET @command = '-SQL "BACKUP DATABASE ' +@dbname + ' TO DISK = ''' +@complete_filepath
+''' WITH NAME = ''' + @BackupFile
+''', DESCRIPTION = '''+ @Description
+''', COPYTO = '''+@filepath_compressed_backup+'compressed_full_backup'
+''', KEYSIZE = 256'
+', MAILTO_ONERROR = '''+@mailto_onerror
+''', PASSWORD = ''xxxxxxx'', INIT"'
exec master..sqlbackup @command
This script works as expected, except for one thing: it sometimes runs slow.
In the logs a backup is reported to take e.g. 9 secs, but the next database in the loop is not backed up for several minutes.
How does sqlbackup works when it comes to the copying?
Will it wait until the copying is done before it continues?
The log says:
SQL Backup log file
1/16/2007 7:03:01 AM: Backing up p127748301 (differential database) on INSTANCES2 instance to:
1/16/2007 7:03:01 AM: BACKUP DATABASE [p127748301] TO DISK = 'd:\backup\p127748301_diff_20070116_070301.zbak' WITH NAME = 'p127748301_diff_20070116_070301.zbak', DESCRIPTION = 'Diff. BACKUP at 20070116_070301.', DIFFERENTIAL, INIT, PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, MAILTO_ONERROR = '[email protected]', COPYTO = '\\192.168.100.179\d$\backup\sql\confirm_dbserver\compressed_diff_backup', COMPRESSION = 1
1/16/2007 7:03:11 AM: Backup data size : 135.313 MB
1/16/2007 7:03:11 AM: Compressed data size: 36.668 MB
1/16/2007 7:03:11 AM: Compression rate : 72.90%
Processed 17232 pages for database 'p127748301', file 'p127748301_dat' on file 1.
Processed 1 pages for database 'p127748301', file 'p127748301_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 17233 pages in 9.021 seconds (15.648 MB/sec).
1/16/2007 7:03:15 AM: Copied d:\backup\p127748301_diff_20070116_070301.zbak to \\192.168.100.179\d$\backup\sql\confirm_dbserver\compressed_diff_backup\p127748301_diff_20070116_070301.zbak.
1/16/2007 7:03:15 AM: SQL Backup process ended.
I am unsure whether the copying is done by the time sqlbackup reports: SQL Backup process ended