Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to monitor backup and restore progress in SQL Server


By:   |   Read Comments (24)   |   Related Tips: More > Backup

Attend a SQL Server Conference for FREE >> click to learn more


Problem

My developers often ask me to create a database backup before any critical deployment or to restore a database to one of the test environments. They often ping me during the process to know the progress and when it will be completed. Sometimes the backup/restore is running through a SQL Agent job which looks hung and I want to know if it's actually doing something. In this tip I have tried to put forth a solution to monitor your backup/restore progress.

Solution

Normally we take a backup or restore a database either through the SSMS or through a script. We might also schedule it using a SQL Agent job. Now let's see how to monitor progress in all the three cases.

Note about permissions: Backup database permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. For restore, if the database being restored does not exist on the server, then the user must have CREATE DATABASE permissions to be able to execute the RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.


Using SSMS

You can backup or restore a database using the GUI and see the stats for the process as follows. Open SSMS, right click on a database then select Task -> Backup or Restore. A screen similar to the below images will open depending if you are doing a backup or restore.

You can monitor the progress on the lower left side of the GUI as shown in the below images. This will give you an idea of the status of the backup or restore.

Backup Screen

using ssms to monitor your backup/restore progress

Restore Screen

you can monitor the progress on the left side of the gui


Using T-SQL

Scripts can be also be used to backup or restore a database and you can use the keyword ‘STATS' in the script to monitor progress.

When using STATS, the number equals the total processing into parts equal to the number. So if you use STATS = 1, this will give you percent by percent completion from 1 to 100%.

Backup showing every 10% of progress

using t-sql to monitor your backup/restore progress

Backup showing every 1% of progress

using stats=1 will give you a percent by percent completion from 1 to 100%

Restore showing every 10% of progress

using t-sql to show the restore progress

Restore showing every 1% of progress

showing every 1% of progress


Using DMVs

If the backup or restore is running from a SQL Agent job or maybe someone kicked off the process from another machine, you can use DMV - sys.dm_exec_requests to find the progress. I really thank Microsoft for introducing DMV's and making a DBA's life a lot easier.

You can run this script, which will give you output simliar to to the screenshot below. Here we can see the percent complete and estimated completion time. This script will work for any backup or restore that is currently running regardless of what method was used to run the backup or restore.

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

using dmv's if the backup/restore is running from a sql agent

If you wanted someone who is not a member of sysadmin role to check backup/restore progress using this script, you can provide permission using the below command:

GRANT VIEW SERVER STATE TO [Login_name]


Note About Restores

I would like to touch base upon one more aspect of SQL Server which you will encounter while restoring databases. You may see that the restore is stuck at 100% or around 99.99% and is not moving further. Sometimes for databases that are very large, TB size databases, it may even take 5 hours for the recovery to complete. To understand this situation we need to understand the different phases that a restore goes through.

The three phases are Data Copy phase, Redo phase and Undo phase. While you may see the restore is 100% complete it's actually only the Data Copy phase that is complete and then SQL proceeds to subsequent phases before the recovery is totally complete.

In the Redo phase, all the committed transactions present in the transaction log when the database was being backed up are rolled forward.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was being backed up are rolled back.

If the database is being restored with NORECOVERY, the Undo phase is skipped. Unfortunately SQL Server does not show the progress during the Redo and Undo phases as it does in the Data Copy phase. So, depending upon the activity in the database at the time it was getting backed up will decide the overall total recovery time.

Next Steps
  • The same DMV can be used to monitor DBCC shrink commands progress as well.
  • Efficient backup strategy is the backbone of disaster recovery and no other high availability solution can replace it.
  • Backups and restores take considerable amount of resources, so you need to plan the backup strategy and timings accordingly.
  • A backup file retention policy should also be created.
  • Keep critical database backups safe using data encryption introduced in SQL Server 2008.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Nitansh Agarwal Nitansh Agarwal is a lead with 4+ years of extensive experience in database administration where he handles large critical databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 28, 2017 - 8:13:11 AM - alpert Back To Top

thanks 

 


Saturday, March 11, 2017 - 12:53:09 AM - Nagarjuna Back To Top
  1. Hi nitansh
  2. Thanks for ur tips
  3. It was very helpful.
  4. Thanks for ur Time

Monday, February 27, 2017 - 11:25:36 AM - pappu Back To Top

Thanks for this article. 

 


Wednesday, December 28, 2016 - 1:14:47 AM - Jeff Moden Back To Top

 Nice tip, Nitansh.  I'm especially appreciative of the "Note about restores" section.  I appreciate the time you took to write this post. 


Monday, October 03, 2016 - 7:46:38 AM - Andre Back To Top

 Nice post! But... Can't make it work when using the SSMS 2016 with a SQL Server 2014. The script returns: 

 

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '.'.

 


Monday, September 19, 2016 - 12:53:42 PM - Mike Back To Top

As mentioned in the section labeled "Notes about Restores", there can be issues with the dmv. The record returned by dm_exec_requests may disappear before the database is fully restored when restoring WITH NORECOVERY. I experience this when I restore from a network location. I have code that checks for a restore to be completed on a secondary Availability Group replica in my staging envinroment before joining the database to the AG. The join fails with error 927 "database is in the middle of a restore". I currently have no reliable way to check for this. So, usually sys.dm_exec_requests is very useful. Just be aware that it is not perfect.

 


Monday, April 04, 2016 - 3:49:33 AM - krishna mutyala Back To Top

Good.

Q)how to find backup running status without using stats in sql server

select * from sys.dm_exec_requests where command in ('BACKUP DATABASE')

select percent_complete from sys.dm_exec_requests where command in ('BACKUP DATABASE')


Monday, March 21, 2016 - 2:42:42 AM - Saravanan A Back To Top

Monitor the progress of backup database script(DMV) is very useful.

Thankyou very much. 

 


Tuesday, March 24, 2015 - 2:53:44 AM - enno Back To Top

Thanx for DMVs script.  Very useful!!


Monday, February 23, 2015 - 5:21:02 AM - Rodrigo Back To Top

Thank you for the post. The DMV query is very useful.


Friday, September 19, 2014 - 2:24:58 PM - Zach Back To Top

This worked perfectly!!!  A backup was taking longer than seven hours.  I ran this and saw that it was 95% done.  Saved me from rebooting out of what I thought was a hung server.

Thank you!

Zach


Friday, January 10, 2014 - 1:56:59 PM - Israel Greß Back To Top

Thank you for the example, it was very useful!

Greetings from Mexico


Thursday, May 30, 2013 - 11:46:08 AM - Tim Back To Top

I've run the script (thx) and it seems that my percent_complete is not changing (67.14183) while the estimated_completion_time keeps marching forward in time.


Friday, May 24, 2013 - 3:09:51 AM - Yuri Back To Top

Hello, Nitansh.

Is there a way to increase progress precision up to 1% while using GUI (SSMS)?


Tuesday, April 23, 2013 - 8:28:42 AM - Sreerag Back To Top

Here is the actual code..

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


Sunday, March 03, 2013 - 6:55:53 PM - Jorge Rivera Back To Top

Thanks for sharing your tip.  It will help a lot, specially when running the script.


Friday, August 03, 2012 - 2:54:20 AM - dinesh Back To Top

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')   this not giving nothing...

What is meaning of it ??

 


Thursday, May 31, 2012 - 8:40:32 AM - Sagar Patel Back To Top

Hello Nitansh,

 

i faced the problem of restoring the database from live server to test server for the testing purpose but i found the error PERMISSION DENIED, so if i want to give RESTORATION rights oif the database to some user , how will i give rights ?

 

Sagar Patel

 

 


Tuesday, January 17, 2012 - 8:26:12 AM - Nitansh Back To Top

Thank you Shashi.




Tuesday, January 17, 2012 - 12:32:22 AM - Shashi Back To Top

Nitansh,

Thank you very much, This is really helpful and you have done a great job!!


Thursday, April 28, 2011 - 9:26:49 PM - Nitansh Back To Top

Thanks Michael.


Thursday, April 28, 2011 - 6:40:07 AM - Michael D Back To Top

Great article, thanks for sharing. 


Tuesday, March 29, 2011 - 10:44:46 AM - Nitansh Back To Top

Thanks Manuel. 


Tuesday, March 29, 2011 - 8:52:25 AM - Manuel A. Rodriguez Back To Top

Hello Nitansh,

I would like to thank you for this very good information. I am asked by our R&D department to restore production databases to our development environment frequently. Most of the databases that they use are very small but occasionally they will ask for one of our larger production databases. The examples that you have presented will help greatly in the process. Once again, thank you for your generous contribution.

Manuel A. Rodriguez


Learn more about SQL Server tools