Database Restoring Status

[Zahidhussain]

Hi All,

We are using Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) in our environment DB01 as a Primary and DB02 as a restoring mode Recently I have checked both DB mdf files and these mdf files are different from each other DB01 mdf file size is 121GB and DB02 is 116 GB my query is how to check restoring mode is on working state or not what is the steps to check the status of Database restoring is it restoring successfully or not.

Kindly response regarding this subject.

Thanks in Advance.

Best Regards

Zahid

[#2]

hi, @Zahidhussain your Question, not clear enough could you please explain more to able to help you.

As I understood from above:- 

That you need to check the restoring progress for your database so I suggest using below Query that will help you to know 

Note:-

the user who runs below should have sysadmin privilege or VIEW SERVER STATE permission

Use master

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 ('RESTORE DATABASE')

Thanks,