DEV Community

Alexey Ryazhskikh
Alexey Ryazhskikh

Posted on

1

How to monitor backup and restore progress in SQL Server

There are few tips that might help you to diagnose problems during backup restore on SQL Server 2016+.

Common restore script:

RESTORE DATABASE [mydb] 
FROM  DISK = N'F:\DatabaseBackups\mydb.bak' 
WITH  FILE = 1,  
MOVE N'mydb' TO N'G:\Databases\mydb.mdf',  
MOVE N'mydb_log' TO N'G:\DatabaseLogs\mydb.ldf',  
NOUNLOAD,  STATS = 1
Enter fullscreen mode Exit fullscreen mode

STATS = 1 - for receiving status messages every 1% of progress

The script to validate backup:

RESTORE verifyonly FROM  DISK = N'F:\DatabaseBackups\mydb.bak'
Enter fullscreen mode Exit fullscreen mode

The script to get backup file statistics, it allows to check restored db size:

Restore Filelistonly FROM  DISK = N'F:\DatabaseBackups\mydb.bak'
Enter fullscreen mode Exit fullscreen mode

Concurrent restore/shrink tasks might block your restoring procedure. You can monitor restoring process status by the following SQL:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,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
WHERE r.command in (
   'BACKUP DATABASE', 'RESTORE DATABASE', 
   'RESTORE HEADERONLY', 'DbccFilesCompact', 'DbccSpaceReclaim')
Enter fullscreen mode Exit fullscreen mode

If you see percent_complete is zero for a log time, you might need to add SQL Server user to Perform Volume Maintenance Tasks security policy via secpol.msc. Check the article:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15

You can check resource consumption with sp_who2 function.
The following script uses a temp table for order/filter operation.

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
--WHERE       SPID = 151
-- Add any sorting of the results here :
ORDER BY    DBName ASC

DROP TABLE #sp_who2
Enter fullscreen mode Exit fullscreen mode

Image of Datadog

The Future of AI, LLMs, and Observability on Google Cloud

Datadog sat down with Google’s Director of AI to discuss the current and future states of AI, ML, and LLMs on Google Cloud. Discover 7 key insights for technical leaders, covering everything from upskilling teams to observability best practices

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more