SQL Server Best Practices: Monitoring Backup and Restore Progress
By Wang Jianming, Senior Engineer
SQL Server Backup & Restore are heavy I/O read and write operations, especially when the database or database backup file is relatively large. It is therefore very important to monitor the backup and restore process. As backup and restore progresses, we can estimate the expected time of completion and any potential impact on the system. This article shows how to monitor SQL Server Backup & Restore progress.
Monitoring Backup & Restore Progress
In a SQL Server database, three main methods are available for monitoring database backup and restore progress:
- Use SSMS to monitor the percentage of backup and restore progress.
- Use the T-SQL stats keyword to show the percentage.
- Use Dynamic View to monitor the percentage of backup and restore completion.
Monitoring the Database Backup Progress
In SSMS, right-click on the database you want to back up, then click on Tasks > Back Up…
In Destination, choose Disk > Add… > choose the local storage path for backup files > OK
In the lower-left corner of the window, the progress of the process is displayed. For example, the progress in the screenshot indicates that 30% of the database has been backed up.
This method enables you to see database backup progress by percentage, but no more details are provided.
Monitoring the Database Restore Progress
This method for monitoring database restore progress is similar to that above, except that it has a different entry point. Entry point to restore a database: right-click on the database you want to restore, then click on Tasks > Restore > Database…
On the Restore Database page, choose Device, click on the Preview button on the right, then click on Add > Add the local backup file > OK
The restore progress bar for the database and the restore percentage appear in the upper-right corner of the following Restore Database page. For example, the database restore progress in the figure is 50%, as shown in the following screenshot:
The methods above describe how to monitor and view the progress of backing up or restoring databases using SSMS. Of course, some prefer to use T-SQL scripts to back up or restore a database. We can also monitor the progress of database backup and restore by adding the stats keyword to the statement. For example, when stats=10, the system displays “** percent processed” in Messages every time it completes 10 percent of the progress.
BACKUP DATABASE [TestBackUpRestore]
TO DISK='C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH STATS=10;
See the screenshot below. In the Messages window, a progress prompt indicating “** percent processed” is provided every time 10 percent of the progress is completed.
Restore the database in the same way by adding the stats keyword. For example:
RESTORE DATABASE [TestBackUpRestore] FROM DISK = N'C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH FILE = 4, NOUNLOAD, STATS = 10GO
Users may forget to add the stats keyword when backing up and restoring the database, so the Messages window does not prompt anything. In this case, how can we monitor the progress of database backup and restore?
In this case, there is no problem because there is another way to monitor database backup and restore progress. If you use Dynamic Management View sys.dm_exec_requests, some additional key information fields can monitor progress. Follow these steps:
database_name = db_name(req.database_id),
[sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (
WHEN - 1 THEN Datalength(txt.TEXT)
END - req.statement_start_offset
) / 2
) + 1),
cpu_time_sec = req.cpu_time / 1000,
granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
wait_time_sec = wait_time/1000,
FROM sys.dm_exec_requests as req WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt
AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')
It is artificially divided into two parts to show the query result set due to the excessively wide result set:
The result provides much important field information, for example:
Command: indicates the command type, which indicates the backup database command here
sql_text: statement details, showing the complete T-SQL statement here
percent_complete: percentage of progress completed, which is 59.67% here
start_time: the time the process started
eta_completion_time: the estimated time that the process will end
and so on. This method is recommended because it can be used to monitor the database backup and restore progress and to obtain more process information.
This method can be used to monitor the backup and restore process as well as any other user process with only a slight modification to the WHERE statement. For example, if you want to monitor the progress of a process, all you need to do is modify the WHERE statement to WHERE req.session_id=xxx.
Getting Backup History Information
The above sections describe how to monitor the progress of the SQL Server Backup & Restore process, and we sometimes encounter the following scenarios: How do you discover or explore the backup history information of a database? Refer to the following code to get the backup history information record of the database TestBackUpRestore.
@database_name = N'TestBackUpRestore'
database_name = bs.database_name,
start_time = bs.backup_start_date,
finish_tiem = bs.backup_finish_date,
time_cost_sec = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date),
back_file = bmf.physical_device_name,
WHEN bs.[type] = 'D' THEN 'Full Backup'
WHEN bs.[type] = 'I' THEN 'Differential Database'
WHEN bs.[type] = 'L' THEN 'Log'
WHEN bs.[type] = 'F' THEN 'File/Filegroup'
WHEN bs.[type] = 'G' THEN 'Differential File'
WHEN bs.[type] = 'P' THEN 'Partial'
WHEN bs.[type] = 'Q' THEN 'Differential partial'
backup_size_mb = ROUND(((bs.backup_size/1024)/1024),2),
compressed_size_mb = ROUND(((bs.compressed_backup_size/1024)/1024),2),
FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK)
INNER JOIN msdb.dbo.backupset bs WITH(NOLOCK)
ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @database_name
ORDER BY bs.backup_start_date DESC
The screenshot is shown in the following figure:
If you use the msdb.dbo.sp_delete_database_backuphistory stored procedure to clear the backup history of the database when you delete the database, you can no longer get the backup history of the database. For example:
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestBackUpRestore'
We have learned how to monitor the progress of SQL Server Backup & Restore to minimize faults and errors. We have also seen three different ways to monitor database backup and restore progress, including using SSMS, T-SQL, and Dynamic View.