By: Nitansh Agarwal | Updated: 2021-10-07 | Comments (37) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Backup
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 or 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 and restore progress.
Solution
Normally we take a backup or restore a database either through SSMS or using
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.
SQL Backup Percentage Complete
There are several ways to get information about the percentage complete for a backup.
SQL Backup Percentage Complete using SSMS GUI
Open SSMS, right click on a database then select Tasks > Back Up. A screen similar to the below image will open. After you select all of the backup options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the backup.
SQL Backup Percentage Complete using T-SQL Script
Scripts can be also be used to do a backup 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 completion from 1 to 100%.
Backup showing every 10% of progress
Backup showing every 1% of progress
SQL Restore Percentage Complete
There are also multiple ways to get restore percentage complete.
SQL Restore Percentage Complete using SSMS GUI
Open SSMS, right click on a database then select Tasks > Restore. A screen similar to the below image will open. After you select all of the restore options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the restore.
SQL Restore Percentage Complete using T-SQL Script
The following screens shows how we can get restore percentage complete using scripts.
Restore showing every 10% of progress
Restore showing every 1% of progress
Getting SQL Backup or SQL Restore Percentage Complete 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 similar 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') 
If you wanted someone who is not a member of sysadmin role to check the backup or restore progress using this script, you can provide permission to them using the below command:
GRANT VIEW SERVER STATE TO [Login_name]
SQL Restore Stuck at 100%
I would like to touch base upon one 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 which was first introduced in SQL Server 2008.
About the author
Nitansh Agarwal is a lead with 4+ years of extensive experience in database administration where he handles large critical databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-10-07
