How can I check database recovery progress in SQL Server?

Question

In a SQL Server database, three main methods are available for monitoring database backup and restore progress: Use SSMS to monitor the percentage of backup and restore progress. Use the T-SQL stats keyword to show the percentage. Use Dynamic View to monitor the percentage of backup and restore completion.

How can I check SQL Server database recovery status?

SQL Server 2008 onwards, the two DMV's sys. dm_exec_requests and sys. dm_tran_database_transactions provides an insight on the different recovery states of the database startup. The other way is by executing the stored procedure “sp_readerrorlog” to get information about the progress of the database recovery.

How do I check the progress of a database restore?

Open SSMS, right click on a database then select Tasks > Restore. A screen similar to the below image will open. After you select all of the restore options and click OK, you can monitor the progress on the lower left side of the GUI as shown in the below image. This will give you an idea of the status of the restore.

How do I find the pending recovery percentage in SQL Server?

You can take a look at the DMO sys. dm_exec_requests. For a backup or restore it will show a percentage complete.

How long does SQL Server recovery take?

No user action is required. SQL Server took 1802 seconds approximately 30 minutes to recover this database. It might take longer depending on the work SQL Server to do to bring database in a consistent state after recovery.

Expert · Answer