How to check SQL Server restore progress

Learn how to check SQL Server restore progress with RESTORE DATABASE WITH STATS, percent_complete and sys.dm_exec_requests, plus restore job monitoring tips.

3 min read0 views
— · 0 votes
Contents
  1. Use RESTORE DATABASE WITH STATS
  2. Query percent_complete in sys.dm_exec_requests
  3. Read progress messages in SSMS
  4. SQL Server restore progress questions
  5. How do I check backup restore progress in SQL Server?
  6. What does percent_complete show during a restore?
  7. Can I watch a restore started by a SQL Server job?
  8. Does restore duration depend on the backup size?

Checking SQL Server restore progress means watching the restore run and confirming how much data has been read and written. You can follow percent_complete in sys.dm_exec_requests, add WITH STATS to the RESTORE DATABASE statement, or read the progress messages in SQL Server Management Studio. Each method shows a different view of the same restore, so pick the one that matches how you started it.

Use RESTORE DATABASE WITH STATS

Adding WITH STATS to a RESTORE DATABASE statement makes SQL Server print a percentage message as the restore advances. The STATS option takes an interval, and the engine reports each time that share of the work finishes. Percentages appear in the messages pane of SQL Server Management Studio when you run the statement there. A restore started with STATS gives you a running percentage without extra queries.

Query percent_complete in sys.dm_exec_requests

The sys.dm_exec_requests dynamic management view reports percent_complete for a restore that is still running. Filter the view by command to find the RESTORE DATABASE row, then read the percent_complete column. The same row shows the start time, so you can compare it with the clock and judge how the work is going. This view answers the progress question from a second session while the restore continues.

Read progress messages in SSMS

SQL Server Management Studio shows restore progress in the messages tab and in the restore dialog when you use the graphical interface. Text output from STATS and the percentage shown by the dialog both come from the running restore. Keep the session open, because closing it can end the restore before it finishes. For a restore started by a scheduled job, open the job history and read the step output instead.

SQL Server restore progress questions

How do I check backup restore progress in SQL Server?

You check backup restore progress in SQL Server by querying sys.dm_exec_requests for the RESTORE DATABASE row and reading percent_complete. Running the restore with WITH STATS gives the same information as printed messages. A restore started in SQL Server Management Studio also shows a percentage in its dialog and messages tab.

What does percent_complete show during a restore?

The percent_complete column shows how much of the restore work SQL Server has finished at the moment you query it. The value rises as pages are read from the backup and written to the database files. A value that stays flat for a long stretch points to a slow storage device or a blocked step, so check the restore job and the disk.

Can I watch a restore started by a SQL Server job?

A restore started by a SQL Server job reports progress through the job step output rather than a live window. Open the job history and read the step messages, which carry the STATS percentages if the command used them. Querying sys.dm_exec_requests from another session still shows the running restore.

Does restore duration depend on the backup size?

Restore duration depends on the size of the backup, the speed of the storage that holds it and the speed of the database files. A larger backup takes longer to read, and slow disks stretch the write phase. Compare the reported percent_complete with the elapsed time to see whether the restore is moving at a steady rate.

Read next

Latest articles