In this article, various methods of monitoring backups and restores of SQL Server databases will be discussed, including tracking current progress and history.

Tracking the status of your initiated backup

If you initiate a backup yourself, you can instruct SQL Server to log the progress status by adding the WITH STATS parameter to the BACKUP DATABASE command. This parameter specifies the percentage interval at which SQL Server should report progress. In this command, the log is updated every 10% progress:

BACKUP DATABASE [Pranas.Events]
TO DISK = 'C:\backups\AdventureWorks.bak'
WITH STATS = 10;

SELECT 
    s.login_name, 
    a.text AS Query, 
    r.start_time, 
    r.percent_complete, 
    DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.command in ('BACKUP DATABASE', 'RESTORE DATABASE');

Checking the history of backups and restores

In addition to directly monitoring backups, there is often a need to understand when, what and where backups were performed on the server. The easiest way to view this information is with a T-SQL query:

SELECT 
    bs.user_name,
    bs.database_name, 
    bs.backup_start_date, 
    bs.backup_finish_date, 
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_secs,
    CASE bs.type 
        WHEN 'D' THEN 'FULL' 
        WHEN 'L' THEN 'Log' 
        WHEN 'I' THEN 'DIFFERENTIAL'
    END AS backup_type, 
    bs.backup_size, 
    bmf.physical_device_name, 
    bs.name AS backupset_name, 
    bs.description
FROM msdb.dbo.backupmediafamily AS bmf
INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id 
WHERE (CONVERT(datetime, bs.backup_start_date) >= GETDATE() - 7) and bs.database_name like '%'
ORDER BY bs.backup_start_date desc;

This query returns all backups made on the server in the last 7 days. However, this query will not show canceled, unsuccessful or incomplete backups.

To view restore history, you can use a query like this:

SELECT 
    rh.destination_database_name, 
    rh.restore_date, 
    CASE 
        WHEN rh.restore_type = 'D' THEN 'Full Database'
        WHEN rh.restore_type = 'F' THEN 'File'
        WHEN rh.restore_type = 'G' THEN 'Filegroup'
        WHEN rh.restore_type = 'I' THEN 'Differential'
        WHEN rh.restore_type = 'L' THEN 'Log'
        WHEN rh.restore_type = 'V' THEN 'Verifyonly'
        ELSE rh.restore_type
    END AS restore_type,
    bs.database_name AS source_database_name,
    bmf.physical_device_name AS backup_file_path
FROM msdb.dbo.restorehistory rh
JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE 
   (CONVERT(datetime, rh.restore_date) >= GETDATE() - 7) and 
   rh.destination_database_name like '%'
ORDER BY rh.restore_date DESC;

This query displays information for the last 7 days, including the database name that was restored, the date and restore type, as well as the source database and file for the restore operation.

Monitoring backups and restores using xEvent

In 2017, SQL Server introduced a new feature called xEvent profiler (Extended Events), which is lighter on the database compared to SQL Profiler and allows monitoring specific aspects of SQL Server operations, including real-time monitoring.

XEvent is integrated with SQL Server Management Studio (SSMS), and it can be configured and managed using T-SQL.

Here’s how you can create a basic xEvent profiler for backups and restores:

CREATE EVENT SESSION 
[BackupsAndRestoreTrace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
WHERE ([trace_level]=('Information of major steps in the operation')))
ADD TARGET package0.event_file(SET filename=N'backup_session_history')
GO

Then, if you click Watch Live Data, you can observe backups and restores tracing in real time:

You don’t necessarily have to keep this window open all the time; you can review all the traces later by opening the package0.event_file in SSMS.

Traces provided by xEvent are much more detailed and complex to interpret compared to previous methods. However, xEvent Profiler offers a wealth of additional data about process states, including information on backups that started but ended with an error.

Backups and restores are stored in SQL Server logs

Another way to view backup history is through SQL Server logs. While they may not contain detailed backup information, they do provide details about unsuccessful backups and restores.

You can view logs through SQL Server Management Studio in Object Explorer under the Management section:

Monitoring backups using third-party tools

Typically, third-party backup utilities provide reports on their operations and store backup histories.

Below, two utilities will be reviewed: sqlbak and sqlbackupandftp.

SqlBak

SqlBak — this agent-based backup utility allows you to manage backups and restores on connected servers. It has several informational forms that allow you to assess the status of backups it has performed.

In the backup job settings, you can see all backups created by this job:

Additionally, the execution log of the backup job is available:

SqlBackupAndFTP

SQLBackupAndFTP is a user-friendly application with an intuitive interface designed to optimize the database backup process.

Information about all recent backups on all servers is displayed on the app dashboard. In the “History & restore” column, you can see all backups created by this job:

Additionally, you can access the execution log of the backup job by clicking Open Log on the selected backup.