Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted April 3, 2017

Displaying Backup or Restore Progress

By Greg Larsen

Have you ever started a database backup or a restore process that runs a long time then wanted to know when it will complete?   If you have then there is an easily way to do this.  Knowing when a database backup or restore operation will completes provides you valuable information, especially when you need to perform follow-on tasks that are waiting for the backup or restore process to complete.

In order to show the backup or restore status you can run the following TSQL statement:

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 

By reviewing this query you can see it is using sys.dm_exec_requests and sys.dm_exec_sql_text to retrieve information for “BACKUP DATABASE” or “RESTORE DATABASE” commands.

Below is a sample of the output from this command that I have reformatted for readability:

SPID   command                          Query                                                                                                                                                                                                                                                            
------ -------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- 
60     BACKUP DATABASE                  BACKUP DATABASE [AdventureWorks2016CTP3] TO  DISK = N'C:\AdventureWorks2016CTP3.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2016CTP3-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10                                       
start_time                         percent_complete     estimated_completion_time
-----------------------            ----------------     -------------------------
2017-03-19 10:11:51.630            43.9362              2017-03-19 10:12:13.460

By reviewing the “percent_complete” column above you can see my backup is 43.9362 percent complete, and then by using the “estimated_completion_time” column you can tell my backup will complete around 10:12:13.460.  You can use the “Query” column  in the output to see the actual BACKUP command that was used to back up my Adventure Works2016CTP3 database.

See all articles by Greg Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM