How to monitor backup and restore progress in SQL Server
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
Restore Screen
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
Backup showing every 1% of progress
Restore showing every 10% of progress
Restore 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')
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.
Copied from:
