Forum Discussion

Zahidhussain's avatar
Zahidhussain
Copper Contributor
Sep 11, 2020

Database Restoring Status

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

SQL Server 2016
  • Mkelsawy's avatar
    Mkelsawy
    Copper Contributor
    Oct 11, 2020

    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,

Resources