Time required to complete database backup and restore in SQL Server


July 04, 2013

Problem

Is there way to know the time SQL Server will take to complete the database backup and the time SQL Server would need to complete the database restore.

Solution

One can use the below mentioned TSQL script to identify the time required to complete the database backup and the time SQL Server would need to complete the database restore. The below mentioned script works with SQL Server 2005 and higher versions.The below query leverages Dynamic Management Views (DMV’s) SYS.DM_EXEC_REQUESTS to get the required information. If you don’t have access to the DMVs then read “Grant Access to DMVs in SQL Server for Non Admin” tip to get access to DMV.

Newsletter Signup

USE MASTER
GO

SELECT
PERCENT_COMPLETE AS [COMPLETED (%)]
,COMMAND AS [ACTIVITY]
,START_TIME AS [ACTIVITY START TIME]
,SD.NAME AS [DATABASE NAME]
,DATEADD(MS,ESTIMATED_COMPLETION_TIME,GETDATE()) AS [TOTAL TIME REMAINING]
,(ESTIMATED_COMPLETION_TIME/1000/60) AS [REMAINING TIME IN MINUTES]
,(ESTIMATED_COMPLETION_TIME/1000) AS [REMAINING TIME IN SECONDS]
FROM SYS.DM_EXEC_REQUESTS ER
INNER JOIN SYS.DATABASES SD
ON ER.DATABASE_ID = SD.DATABASE_ID
WHERE COMMAND LIKE '%RESTORE%'
OR COMMAND LIKE '%BACKUP%'
AND ESTIMATED_COMPLETION_TIME > 0
GO

Conclusion

The script mentioned in this tip can be very useful to DBA to identify the amount of time required to backup or restore a very large database.


Share this Article

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."