How to Monitor SQL Server Backup and Restore Progress Effectively

Scenario Introduction

Because backup and restore operations in SQL Server involve heavy I/O, especially for large databases, it is essential to monitor their progress to estimate completion time and assess system impact.

Monitoring Backup Progress with SSMS

In SQL Server Management Studio, right‑click the target database, choose Tasks → Back Up… , select a disk destination, and start the backup. The lower‑left corner of the dialog shows a progress bar with a percentage (e.g., 30%). This method displays only the overall percentage without detailed metrics.

Monitoring Restore Progress with SSMS

Restoring follows a similar UI path: right‑click the database, select Tasks → Restore → Database… , choose the backup file via Device → Add , and start the restore. The top‑right corner of the restore dialog displays a progress bar (e.g., 50%).

Monitoring with T‑SQL (STATS option)

For script‑based operations, add the STATS=10 option to the BACKUP or RESTORE command. SQL Server then prints a message like “ 10 percent processed ” in the Messages window after each 10% increment.

BACKUP DATABASE [TestBackUpRestore]
TO DISK='C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH STATS=10;

Similarly, for restore:

USE [master]
RESTORE DATABASE [TestBackUpRestore] FROM DISK = N'C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH FILE = 4, NOUNLOAD, STATS = 10;
GO

Monitoring with Dynamic Management Views (DMV)

If the STATS option is omitted, you can query sys.dm_exec_requests to obtain progress information. A typical query filters on the session ID and extracts fields such as percent_complete, start_time, and estimated_completion_time. The result set provides both the percentage and additional details about the running backup or restore task.

The key columns include:

command : type of operation (e.g., BACKUP DATABASE)

sql_text : full T‑SQL statement

percent_complete : progress percentage (e.g., 59.67%)

start_time : when the operation began

estimated_completion_time : projected finish time

This method not only monitors backup/restore but can be adapted to track any long‑running user process by adjusting the WHERE clause.

Retrieving Backup History

To view historical backup records for a database, you can query the MSDB tables (e.g., backupset, backupmediafamily). The following script returns the backup history for the TestBackUpRestore database:

SELECT bs.database_name, bs.backup_start_date, bs.backup_finish_date,
       bs.type AS backup_type, bm.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE bs.database_name = N'TestBackUpRestore'
ORDER BY bs.backup_start_date DESC;

Be aware that if you delete the backup history using msdb.dbo.sp_delete_database_backuphistory, the records will no longer be available.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestBackUpRestore';
GO

Final Summary

After a series of six articles covering SQL Server backup and restore strategies, this piece completes the series by detailing three practical monitoring techniques (SSMS UI, T‑SQL STATS, DMV) and showing how to query backup history, helping DBAs maintain visibility over critical data protection operations.