Knowledge Base

Backups and restores taking longer each time they run

Category: Troubleshooting & error messages
Date: 20 May 2009
Product: SQL Backup
SQL Backup may take progressively longer to perform backup and restore jobs the more that it is used. There are some maintenance tasks that SQL Backup performs with each backup, including deleting old backup files and deleting old backup history.

If you have configured automatic deletion of backups using the ERASEFILES keyword as part of your backup job, it causes SQL Backup to read all backup files in the backup folder location and examine them to determine if they are older than the date specified in the ERASEFILES keyword. This can slow down the deletion of the old backup files as it has to check each file.

It may alleviate the problem somewhat to store backups for each database on your server into a separate folder. SQL Backup's file erasure routine will only look in the folder location for the database currently being backed up. If you change the backup folder in the schedule wizard to \\sqlbackup01\data\SQLBACKUP\<DATABASE>, for instance, this will create a backup folder location for each individual database being backed up rather than simply writing the backup files for all databases into the same folder.

Another feature of SQL Backup that can cause a lot of post-processing is the "delete old backup history" server option. When your MSDB system database contains a lot of history, the processing time can become excessive, and is exacerbated when many concurrent SQL Backup jobs are completing around the same time. If this becomes a problem, then it may be desirable to unset the option to delete old backup history in the server options and perform this task in a completely separate SQL Agent job as described here:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase%5cSQL_Backup%5cKB200801000214.htm