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:

  1. Use SSMS to monitor the percentage of backup and restore progress.
  2. Use the T-SQL stats keyword to show the percentage.
  3. Use Dynamic View to monitor the percentage of backup and restore completion.

Use SSMS

Monitoring the Database Backup Progress

In SSMS, right-click on the database you want to back up, then click on Tasks > Back Up…

Image for post
Image for post

In Destination, choose Disk > Add… > choose the local storage path for backup files > OK

Image for post
Image for post

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…

Image for post
Image for post

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

Image for post
Image for post

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:

Image for post
Image for post

Use T-SQL

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.

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.

Image for post
Image for post

Note:

Restore the database in the same way by adding the stats keyword. For example:

Use DMV

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:

It is artificially divided into two parts to show the query result set due to the excessively wide result set:

Image for post
Image for post
Image for post
Image for post

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.

Note:

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.

The screenshot is shown in the following figure:

Image for post
Image for post

Special attention:

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:

Summary

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.

Reference:https://www.alibabacloud.com/blog/sql-server-best-practices-monitoring-backup-and-restore-progress_594391?spm=a2c41.12517223.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store