CODEWITHSUNDEEP
sql-serverbackuprestore
Veldmuis
Veldmuis

Reputation: 4888

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

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.

Upvotes: 119

Views: 319053

Answers (19)

QuilleyPowers
QuilleyPowers

Reputation: 110

I love the script below for monitoring backups or anything in general going on (doesn't work for monitoring encrypt/decrypt). I copied it from someone smarter than me and just made the fairly obvious tweak to get the completion estimation time.

SELECT      percent_complete,estimated_completion_time/(1000*60) AS EstimatedCompletionTimeInMinutes
            ,r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL], 
            Status,command,wait_type,wait_time,wait_resource, 
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time

Upvotes: 0

Kamal Hossain
Kamal Hossain

Reputation: 1

you can run the following to get a detail reports on MS SQL for your restore progress-

exec msdb.dbo.rds_task_status;

Upvotes: 0

Portman
Portman

Reputation: 31995

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.

Upvotes: 13

Veldmuis
Veldmuis

Reputation: 4888

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')

Upvotes: 264

Ben
Ben

Reputation: 318

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')

Upvotes: 4

Promise Preston
Promise Preston

Reputation: 29098

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:

enter image description here

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

Upvotes: 1

bsplosion
bsplosion

Reputation: 2886

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.

Upvotes: 1

Zaalouni Mohamed
Zaalouni Mohamed

Reputation: 71

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.

Upvotes: 2

Zaalouni Mohamed
Zaalouni Mohamed

Reputation: 71

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')

Upvotes: 5

user7488971
user7488971

Reputation:

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>50 and command IN ('RESTORE DATABASE','BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

Upvotes: 7

ahsan Mumtaz Abbasi
ahsan Mumtaz Abbasi

Reputation: 21

simply run bkp_status on master db you will get backup status

Upvotes: -1

RC Bird
RC Bird

Reputation: 11

I am using sp_whoisactive, very informative an basically industry standard. it returns percent complete as well.

Upvotes: 0

BMDaemon
BMDaemon

Reputation: 61

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

Upvotes: 3

Liam Fleming
Liam Fleming

Reputation: 1116

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' )

Upvotes: 0

Wilfred Kimani
Wilfred Kimani

Reputation: 171

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')

Upvotes: 17

Shahbaz I Shaikh
Shahbaz I Shaikh

Reputation: 61

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')

Upvotes: 5

Allen
Allen

Reputation: 181

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

Upvotes: 18

David L Morris
David L Morris

Reputation: 1513

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).

Upvotes: 4

Pavel Chuchuva
Pavel Chuchuva

Reputation: 22475

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

Upvotes: 3

Related Questions