Backup or restore operations may take longer than expected. This may be caused by a number of processes run by SQL Backup Pro or by the SQL Server. 

To check the progress of any SQL Backup Pro backup or restore operations currently executing on a SQL Server, run the sqbstatus extended stored procedure against the master database:

execute master..sqbstatus

The results are listed per database:

• Processed (bytes) represents the uncompressed size of the data that has been processed. 
• Compressed (bytes) represents the compressed size of the data that has been processed.

If sqbstatus reports 0 bytes, this indicates that SQL Server is performing tasks that must be completed before the backup or restore operation can begin, and is not currently able to accept data from SQL Backup Pro. For information on identifying the tasks SQL Server is performing, see SQL Server processes below.

Deleting old backup files

If you include the ERASEFILES, ERASEFILES_ATSTART or ERASEFILES_REMOTE option in a BACKUP command, or select the option to Delete existing backup files in this folder for selected database in the Back Up or Schedule Backup Jobs wizard, SQL Backup Pro deletes backups of the same database and type from the backup folder as part of the backup operation. To identify the files to delete, SQL Backup Pro reads the headers of all the files in the backup folder. The larger the number of files stored in the folder, the longer it will take SQL Backup Pro to read all of the file headers. 

To reduce the number of file headers SQL Backup Pro has to read each time, store backups in folders according to database name and backup type. You can do this automatically, by including the <DATABASE> and <TYPE> tags in folder paths. For example, the following command will create full backups of Database 1, Database 2 and Database 3 in C:\Backups\Database 1\Full, C:\Backups\Database 2\Full and C:\Backups\Database 3\Full respectively:

"BACKUP DATABASES [Database 1, Database 2, Database 3] TO DISK = 'C:\Backups\<DATABASE>\<TYPE>\<DATETIME ddmmyy>.sqb' WITH ERASEFILES = 7"

The backup file names are based on the date and time of the backup

To avoid having to write out the tags each time, you can include tags in the default backup location used by SQL Backup Pro, then use the <AUTO> tag in the BACKUP command. When using the Back Up or Schedule Backup Jobs wizard, the backup location is populated with the default location.

• For more information about setting the default backup location, see File management options.
• For more information about the tags you can use, see File location tags.
• From SQL Backup 9.1.3 the backup agent will retry to delete the file 12 times with a sleep of 5 seconds between attempts.

Deleting backup and restore history

If you have selected the option to Delete all backup and restore history older than <n Days | Hours> (available from Tools > Server Options > File Management), SQL Backup Pro uses the stored procedure msdb..sp_delete_backuphistory to delete history from the msdb database when the graphical user interface is running.  For more information about this option, see File management options.

If the msdb database contains a lot of history, or if multiple SQL Backup Pro jobs are completing at the same time, deleting the history can slow down backup and restore operations. You may find it helpful to add indexes to the backup and restore history tables in the msdb database. For more information, refer to your SQL Server documentation. Alternatively, clear this option and run the stored procedure manually at a convenient time or as a scheduled task. For more information, see Deleting backup and restore history manually.

SQL Server processes

To find out which processes are being run by SQL Server as part of a backup or restore, and how long those processes take, set a trace flag on the SQL Server master database:

DBCC TRACEON (3004, 3605, -1)

Setting the trace flag causes additional information to be written to the SQL Server logs. To view the logs, open SQL Server Management Studio and in Object Explorer open Management > SQL Server Logs > Current.