Beyond the GUI: Scripting Your Way to SQL Server Backup and Restore Monitoring

Using sys.dm_exec_requests:

This system view provides information about currently running requests on the server, including backups and restores. Here's an example script:

SELECT 
    session_id,
    db_name(database_id) AS database_name,
    command,
    percent_complete,
    CONVERT(VARCHAR(20), start_time, 120) AS start_time,
    CONVERT(DECIMAL(6, 2), [total_elapsed_time] / 1000.0 / 60.0) AS elapsed_minutes
FROM sys.dm_exec_requests AS reqests
WHERE command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

Explanation:

• This script filters sys.dm_exec_requests for commands related to backups and restores.
• It retrieves information like session_id, database_name, command type (backup/restore), percent_complete, start_time, and calculates the elapsed_minutes.

Using STATS keyword with BACKUP command:

While not ideal for live monitoring, the STATS keyword with the BACKUP command provides progress information during the backup itself. Here's an example:

BACKUP DATABASE MyDatabase TO DISK = N'C:\Backups\MyDatabase.bak' WITH STATS;

This command will display backup progress as a percentage in the SSMS output window.

Related Issues and Solutions:

• Limited information: These methods only provide basic information like completion percentage and elapsed time.
• Security: Using sys.dm_exec_requests requires appropriate permissions.

Alternatives:

• SSMS GUI: For a user-friendly experience, consider using the SSMS GUI to monitor backups and restores visually.
• Management tools: Some SQL Server management tools offer advanced monitoring capabilities for backups and restores.