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 estimated completion. Sometimes the backup or restore is running through a SQL Server 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 percentage complete in SQL Server.
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, the user needs CREATE DATABASE permissions to execute the RESTORE if the database does not exist. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.
Backup Percentage Complete in SQL Server
There are several ways to get information about the percentage complete for a backup.
Backup Percentage Complete in SQL Server 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 Completion Percentage using T-SQL Script
T-SQL scripts can execute database backups. Specify the STATS keyword 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 Completion Percentage showing every 10% of progress
Backup Completion Percentage showing every 1% of progress
Restore Percentage Complete in SQL Server
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.
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. Sometimes the restore appears stuck at 100% or around 99.99%. For very large databases, (i.e. TB size), it may even take several hours for the recovery to complete. To understand the restore completion percentage, 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.
