Monitorando o progresso de Backup e Restore no SQL Server (sem interface gráfica)

Quando um backup ou restore é feito pelo SQL Server Management Studio, é fácil acompanhar o andamento pela própria tela da ferramenta. Mas nem sempre isso é possível: o DBA pode não ter acesso à interface gráfica, ou a operação pode estar rodando via JOB do SQL Server Agent ou por script T-SQL direto — cenários comuns quando se trabalha com bases muito grandes e é preciso dar uma previsão de término da atividade.

Para esses casos, a query abaixo mostra o andamento de qualquer backup ou restore em execução, direto pelas Dynamic Management Views (DMVs) do SQL Server.

Query de monitoramento

SELECT
    r.session_id                                       AS SPID,
    r.command                                           AS Comando,
    a.text                                              AS Query,
    r.start_time                                        AS InicioExecucao,
    r.percent_complete                                  AS PercentualCompleto,
    DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS PrevisaoTermino,
    DATEADD(SECOND, r.total_elapsed_time / 1000, r.start_time)     AS TempoDecorrido
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE '%BACKUP%' OR r.command LIKE '%RESTORE%'
ORDER BY r.percent_complete DESC;

(Em relação à versão original, ampliamos o filtro de r.command in ('BACKUP DATABASE','RESTORE DATABASE') para LIKE '%BACKUP%' OR LIKE '%RESTORE%', pois o SQL Server também reporta com esses mesmos campos operações como BACKUP LOG e RESTORE LOG, que ficavam de fora do filtro original.)

Pré-requisito: para executar essa consulta, o login precisa da permissão VIEW SERVER STATE (ou VIEW SERVER PERFORMANCE STATE, a partir do SQL Server 2022).

Exemplo de resultado

SPID	Comando	Query	InicioExecucao	PercentualCompleto	PrevisaoTermino
56	RESTORE DATABASE	RESTORE DATABASE [WEBMUNDIDB] FROM DISK = N'\\serverwm1\Backup\WEBMUNDIDB.bak' WITH FILE = 1, MOVE N'WEBMUNDIDB' TO N'D:\DB\WEBMUNDIDB.mdf', MOVE N'WEBMUNDIDB_log' TO N'D:\DB\WEBMUNDIDB_log.ldf', NOUNLOAD, REPLACE, STATS = 5	2016-05-04 17:26:22.663	22,33	2016-05-04 20:57:37.223

O que significa cada coluna

• SPID: Server Process ID, o identificador da sessão que está executando o comando.
• Comando: o tipo de operação em andamento (BACKUP DATABASE, RESTORE DATABASE, BACKUP LOG etc.).
• Query: o texto completo do comando T-SQL que está sendo executado.
• InicioExecucao: horário em que a execução começou.
• PercentualCompleto: percentual já concluído da operação.
• PrevisaoTermino: horário estimado de término, calculado somando o estimated_completion_time (em milissegundos) ao horário atual.
• TempoDecorrido (nova coluna): quanto tempo já se passou desde o início da execução — útil para comparar com a previsão de término e avaliar se o ritmo está dentro do esperado.

Dica extra: acompanhar em intervalos, sem precisar re-executar manualmente

Para observar a evolução do percentual ao longo do tempo sem ficar rodando a query manualmente, é possível usar o comando WAITFOR em um loop simples (útil em uma sessão dedicada de monitoramento):

WHILE 1 = 1
BEGIN
    SELECT
        r.session_id AS SPID,
        r.command AS Comando,
        r.percent_complete AS PercentualCompleto,
        DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS PrevisaoTermino
    FROM sys.dm_exec_requests r
    WHERE r.command LIKE '%BACKUP%' OR r.command LIKE '%RESTORE%';

    IF NOT EXISTS (
        SELECT 1 FROM sys.dm_exec_requests
        WHERE command LIKE '%BACKUP%' OR command LIKE '%RESTORE%'
    )
        BREAK;

    WAITFOR DELAY '00:00:10'; -- atualiza a cada 10 segundos
END;

Vai além de backup e restore

Vale saber que a coluna percent_complete de sys.dm_exec_requests não é exclusiva de backup/restore: ela também é preenchida para outras operações longas, como DBCC CHECKDB, DBCC SHRINKFILE, ALTER INDEX ... REBUILD (quando ONLINE = ON) e recuperação de banco de dados no startup. Ou seja, a mesma query serve de base para monitorar diversas tarefas de manutenção de longa duração — basta ajustar o filtro da coluna command.

Conclusão

Monitorar backup e restore sem interface gráfica é simples com sys.dm_exec_requests combinada com sys.dm_exec_sql_text. Ajustando o filtro de comando e adicionando o tempo decorrido junto da previsão de término, essa consulta se torna uma ferramenta útil não só para backup e restore, mas para acompanhar qualquer operação de manutenção longa no SQL Server.

Espero que esta matéria tenha sido útil e tenha ajudado a aumentar seus conhecimentos no banco de dados SQL Server da Microsoft.

Se você gostou, compartilhe estas dicas com um amigo ou participe de nossas redes sociais. Caso queira nos incentivar a criar mais conteúdos como este, prestigie nossos anunciantes — essa é uma forma de apoiar o Web Mundi e fazer com que mais pessoas tenham acesso a este tipo de informação.

Um grande abraço!

Renato Sanches