DEV Community

loading...

How to monitor backup and restore progress in SQL Server

musukvl profile image Alexey Ryazhskikh ・2 min read

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

Discussion (0)

pic
Editor guide