SQL-Server Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process

Question

When I backup or restore a database using MS SQL Server Management Studio  I get a visual indication of how far the process has progressed  and thus how much longer I still need to wait for it to finish  If I kick off the backup or restore with a script  is there a way to monitor the progress  or do I just sit back and wait for it to finish  hoping that nothing has gone wrong    Edited  My need is specifically to be able to monitor the backup or restore progress completely separate from the session where the backup or restore was initiated

User · Answer

SELECT  session id as SPID  command  start time  percent complete          dateadd second estimated completion time 1000  getdate    as estimated completion time          a text AS Query  FROM sys dm exec requests r CROSS APPLY sys dm exec sql text r sql handle  a WHERE r command in   BACKUP DATABASE    BACKUP LOG    RESTORE DATABASE    RESTORE LOG

User · Answer

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

User · Answer

If you know the sessionID then you can use the following   SELECT   FROM sys dm exec requests WHERE session id   62   Or if you want to narrow it down   SELECT command  percent complete  start time FROM sys dm exec requests WHERE session id   62

User · Answer

Add STATS 10 or STATS 1 in backup command   BACKUP DATABASE  xxxxxx  TO  DISK   N E   Bachup DB bak  WITH NOFORMAT  NOINIT    NAME   N xxxx-Compl  te Base de donn  es Sauvegarde   SKIP  NOREWIND  NOUNLOAD  COMPRESSION   STATS   10 GO

User · Answer

For anyone running SQL Server on RDS  AWS   there s a built-in procedure callable in the msdb database which provides comprehensive information for all backup and restore tasks   exec msdb dbo rds task status    This will give a full rundown of each task  its configuration  details about execution  such as completed percentage and total duration   and a task info column which is immensely helpful when trying to figure out what s wrong with a backup or restore

User · Answer

I found this sample script here that seems to be working pretty well   SELECT r session id r command CONVERT NUMERIC 6 2  r percent complete  AS  Percent Complete  CONVERT VARCHAR 20  DATEADD ms r estimated completion time GetDate    20  AS  ETA Completion Time   CONVERT NUMERIC 10 2  r total elapsed time 1000 0 60 0  AS  Elapsed Min   CONVERT NUMERIC 10 2  r estimated completion time 1000 0 60 0  AS  ETA Min   CONVERT NUMERIC 10 2  r estimated completion time 1000 0 60 0 60 0  AS  ETA Hours   CONVERT VARCHAR 1000   SELECT SUBSTRING text r statement start offset 2  CASE WHEN r statement end offset   -1 THEN 1000 ELSE  r statement end offset-r statement start offset  2 END  FROM sys dm exec sql text sql handle    AS  SQL  FROM sys dm exec requests r WHERE command IN   RESTORE DATABASE   BACKUP DATABASE

User · Answer

Script to check the Backup and Restore progress in SQL Server    Many times it happens that your backup  or restore  activity has been started by another Database Administrator or by a job  and you cannot use the GUI anything else to check the progress of that Backup   Restore   By combining multiple commands  I have generated below script which can give us a summary of current backups and restores which are happening on the server   select  r session id   r blocking session id   db name database id  as  DatabaseName   r command    SQL QUERY TEXT    Substring Query TEXT   r statement start offset   2    1                                  CASE r statement end offset                     WHEN - 1                         THEN Datalength Query TEXT                      ELSE r statement end offset                     END - r statement start offset                     2                 1                    SP Name   Coalesce Quotename Db name Query dbid     N      Quotename Object schema name Query objectid  Query dbid     N            Quotename Object name Query objectid  Query dbid         r percent complete  start time  CONVERT VARCHAR 20   DATEADD ms   estimated completion time   GETDATE     20  AS  ETA COMPLETION TIME   CONVERT NUMERIC 6  2   r  total elapsed time    1000 0   60 0  AS  Elapsed MIN   CONVERT NUMERIC 6  2   r  estimated completion time    1000 0   60 0  AS  Remaning ETA MIN   CONVERT NUMERIC 6  2   r  estimated completion time    1000 0   60 0  60 0  AS  ETA Hours   wait type  wait time 1000 as Wait Time Sec   wait resource from sys dm exec requests r  cross apply sys fn get sql r sql handle  as Query where r session id gt 50 and command IN   RESTORE DATABASE   BACKUP DATABASE    RESTORE LOG    BACKUP LOG

User · Answer

Yes  If you have installed sp who2k5 into your master database  you can simply run   sp who2k5 1 1   The resultset will include all the active transactions  The currently running backup s  will contain the string  BACKUP  in the requestCommand field  The aptly named percentComplete field will give you the progress of the backup   Note  sp who2k5 should be a part of everyone s toolkit  it does a lot more than just this

User · Answer

Try wih    SELECT   FROM sys dm exec requests where command like   BACKUP    SELECT command  percent complete  start time FROM sys dm exec requests where command like   BACKUP    SELECT command  percent complete total elapsed time  estimated completion time  start time   FROM sys dm exec requests   WHERE command IN   RESTORE DATABASE   BACKUP DATABASE

User · Answer

I think the best way to find out how your restore or backup progress is by the following query    USE master  GO 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   GO   The query above  identify the session by itself and perform a percentage progress every time you press F5 or Execute button on SSMS   The query was performed by the guy who write this post

User · Answer

I am using sp whoisactive  very informative an basically industry standard  it returns percent complete as well

User · Answer

Here s a simple script that generally does the trick for me   SELECT command  percent complete total elapsed time  estimated completion time  start time   FROM sys dm exec requests   WHERE command IN   RESTORE DATABASE   BACKUP DATABASE

User · Answer

simply run bkp status on master db you will get backup status

User · Answer

To monitor the backup or restore progress completely separate from the session where the backup or restore was initiated  No third party tools required  Tested on Microsoft SQL Server 2012   SELECT percent complete    FROM sys dm exec requests WHERE command In    RESTORE DATABASE    BACKUP DATABASE

User · Answer

Use STATS in the BACKUP command if it is just a script   Inside code it is a bit more complicated   In ODBC for example  you set SQL ATTR ASYNC ENABLE and then look for SQL STILL EXECUTING return code  and do some repeated calls of SQLExecDirect until you get a SQL SUCCESS  or eqiv

User · Answer

Use STATS option  http   msdn microsoft com en-us library ms186865 aspx

User · Answer

I had a similar issue when working on Database restore operation on MS SQL Server 2012  However  for my own scenario  I just needed to see the progress of the DATABASE RESTORE operation in the script window All I had to do was add the STATS option to the script  USE master  GO  ALTER DATABASE mydb SET SINGLE USER WITH ROLLBACK IMMEDIATE  GO      RESTORE DATABASE mydb     FROM DISK    C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL Backup my db 21-08-2020 bak      WITH REPLACE           STATS   10           RESTART      MOVE  my db  TO  C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA my db mdf       MOVE  my db log  TO  C  Program Files Microsoft SQL Server MSSQL12 MSSQLSERVER MSSQL DATA mydb log ldf  GO      ALTER DATABASE mydb SET MULTI USER  GO  And then I switched to the Messages tab of the Script window to see the progress of the DATABASE RESTORE operation   If you want to get more information after the DATABASE RESTORE operation you can use this command suggested by eythort  SELECT command  percent complete  start time FROM sys dm exec requests where command    RESTORE DATABASE   That s all  I hope this helps

[sql-server] SQL-Server: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?

Examples related to sql-server

Examples related to backup

Examples related to restore