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.
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
the user who runs below should have sysadmin privilege or VIEW SERVER STATE permission
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')