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.
In a SQL Server database, three main methods are available for monitoring database backup and restore 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.
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:
USE [master] RESTORE DATABASE [TestBackUpRestore] FROM DISK = N'C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH FILE = 4, NOUNLOAD, STATS = 10 GO
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:
USE master GO SELECT req.session_id, database_name = db_name(req.database_id), req.status, req.blocking_session_id, req.command, [sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, ( ( CASE req.statement_end_offset WHEN - 1 THEN Datalength(txt.TEXT) ELSE req.statement_end_offset END - req.statement_start_offset ) / 2 ) + 1), req.percent_complete, req.start_time, cpu_time_sec = req.cpu_time / 1000, granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.), req.reads, req.logical_reads, req.writes, 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_type, wait_time_sec = wait_time/1000, wait_resource FROM sys.dm_exec_requests as req WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt WHERE req.session_id>50 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.
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.
use msdb GO DECLARE @database_name sysname ; SELECT @database_name = N'TestBackUpRestore' ; SELECT bs.server_name, bs.user_name, 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, backup_type = CASE 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' END, backup_size_mb = ROUND(((bs.backup_size/1024)/1024),2), compressed_size_mb = ROUND(((bs.compressed_backup_size/1024)/1024),2), bs.first_lsn, bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn, bs.software_major_version, bs.software_minor_version, bs.software_build_version, bs.recovery_model, bs.collation_name, bs.database_version 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' GO
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.
SQL Server Best Practices: Using Asymmetric Keys to Implement Column Encryption
SQL Server Best Practices: Database Recovery Models and Backups
11 posts | 0 followersFollow
Alibaba Clouder - August 1, 2019
Cherish Wang - February 20, 2019
Alibaba Clouder - August 6, 2020
Cherish Wang - February 20, 2019
Alibaba Clouder - July 22, 2020
Cherish Wang - January 17, 2019
11 posts | 0 followersFollow
Fully managed and less trouble database servicesLearn More
Block-level data storage attached to ECS instances to achieve high performance, low latency, and high reliabilityLearn More
A reliable, cost-efficient backup service for continuous data protection.Learn More
Tair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.Learn More
More Posts by Cherish Wang
Raja_KT March 22, 2019 at 4:00 pm
MS SQL products are easy , most of the time as they have GUI , IDE... and just follow the steps :)