SQL Server Best Practices: Monitoring Backup and Restore Progress

In this article of the SQL Server best practices series, we will learn how to monitor the progress of SQL Server Backup & Restore to minimize faults and errors.

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.

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...

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:

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.

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.

Note:

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

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:

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.

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.

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:

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:

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

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.

Community

SQL Server Best Practices: Monitoring Backup and Restore Progress

Monitoring Backup & Restore Progress

Use SSMS

Monitoring the Database Backup Progress

Monitoring the Database Restore Progress

Use T-SQL

Use DMV

Getting Backup History Information

Summary

Read previous post:

Read next post:

Cherish Wang

You may also like

Comments

Raja_KT March 22, 2019 at 4:00 pm

Cherish Wang

Related Products

Database Backup

Tair

Database Security Solutions

Backup and Archive Solution