How to monitor backup and restore progress in SQL | Easy 4 Methods

backup and restore progress : To monitor backup and restore Percentage in SQL Server To Check Backup and Restore % in SQL there are different method that you can check the backup and restore percentage in SQL Server.

backup and restore progress

IN SQL Server Backup and Restore Database is one of the most common Work of Database Administrator.
To check the Backup and restore Status and Percentage in SQL server.

There are different ways like manually in JOB and system views or using SQL Script in Master Database.
The Methods of checking status and progress of database backup and restore are depending on your version of SQL server.

Below is the Common Methods in SQL server to Check the Progres or Percentage completed for Backup or restore database.

If you are Backup or restore your database using SQL Server Agent Job, then you can see the Progress and Percentage in SSMS interface.
You will see a Action and Status while Executing Job for Backup or Restore. But actually, you cannot see Percentage in UI for SQL Agent Job.
for That You need to Execute SQL Script to check your Job Execution status percentage for Backup or Restore.

Below is the Script to check Backup and Restore Percentage in SQL Server when backup or restore is ongoing.
Below are the common Methods to check the BACKUP and RESTORE database Percentage.

https://stackoverflow.com/

Methods to check backup and restore progress/ Percentage..

Method 1.

Backup and Restore Percentage Query


USE MASTER
SELECT 
  percent_complete
  FROM sys.dm_exec_requests  d
   CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) b
WHERE d.command  in ('backup database','restore database')

Method 2.

Use below SQL Select Query on Master database.

USE master 
select * from sys.dm_exec_requests

Above Query will show all the data from sys.dm_exec_requests in which currently running sessions and its details are available you can filter them using Command column.

Method 3.

Using SSMS

If you are restoring or backup database manually using SQL server Management Studio SSMS then you can see Progress of your backup and Restore database
using on same screen Interface (User interface) @ Bottom of Left side ‘Progress’ for backup and restore will be show in Top right side.

Click How to Restore Database in SSMS

The percent_complete column will show you the progress of the operation as a percentage.

Method 4.

Using Procedure

use master 
EXEC sp_who2;

You can see the status of Progress by Executing Stored procedure sp_who2. In This Procedure you will see all the details of running queries and operations in the database.
by using sp_who2 you can track the status of your execution.

Click Here how to take Database Backup in SQL



	-------CHECK BACKUP and RESTORE Percentage-----

	----Method 1.---
	SELECT 
  percent_complete
  FROM sys.dm_exec_requests  d
   CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) b
WHERE d.command ='backup database'


SELECT 
  percent_complete
  FROM sys.dm_exec_requests  d
   CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) b
WHERE d.command ='restore database'


	----Method 2.---
	select * from sys.dm_exec_requests 

	----Method 3.---
	SELECT session_id ProcessID, command [Baclup or Restore],
	 percent_complete [% Done] FROM sys.dm_exec_requests
		WHERE command IN ('BACKUP DATABASE', 'RESTORE DATABASE');

	----Method 4.---
	EXEC sp_who2;

backup and restore progress.

Table of Contents