Time required to complete database backup and restore in SQL Server
July 04, 2013
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.
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.
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
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.
Continue Free Learning...
Geeks who read this article also read…
- How to Export records from SQL Server to Text File using BCP
- Update Statistics for all databases in SQL Server
- Different ways to retrieve System and SQL Server Information
- How to configure SQL Server Agent to Restart SQL Server and SQL Server Agent Services Automatically
- How to connect to a named instance of SQL Server?
- Fix Index cannot be reorganized because page level locking is disabled error in SQL Server
- Grant Access to DMVs in SQL Server for Non Admin
- How to Improve SQL Server Replication Performance
- Unable to create or open SSIS projects or maintenance plans in SQL Server 2012
- Performance Dashboard Reports in SQL Server 2012
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…