When will the Database Backup/Restore Complete?

Often time, when doing an ad-hoc backup or restore on SQL Server database (especially for a very large database), we want to get some estimate as to when the backup or restore process will complete. As normally, after the backup or restore process has been completed, you will need to perform some additional tasks. So getting an estimate of when the Backup/Restore process takes will help you to plan ahead.

Using the STATS Option in Backup or Restore Command

For the longest time, I have been using the STATS option when doing ad-hoc backup or restore database in SQL Server. Basically what the STATS option does is that during the backup or restore process, every time the backup or restore process reach certain percentage (by default, if we don’t specify the percentage with the option, it is every 10%), it will display the percentage completed on the Messages window of Microsoft SQL Server Management Studio. Examples of using the STATS option in backup command:

  1: BACKUP DATABASE AdventureWorks2008R2
  2: TO DISK = 'C:\Backup\AdventureWorks2008R2.bak'
  3: WITH COPY_ONLY, STATS=5

This should backup the AdventureWorks2008R2 database onto C:\Backup folder. And, since I use the STATS=5 option, every 5%, I will get a display in the Messages tab, it should be something similar to:

You can do a similar thing with the restore command also. An example of the restore command script:

  1: RESTORE DATABASE AdventureWorks2008R2
  2: FROM DISK = 'C:\Backup\AdventureWorks2008R2.bak'
  3: WITH RECOVERY, STATS=5

Querying the Dynamic Management Objects

Starting with SQL Server 2005, we have dynamic management objects (DMO). The dynamic management objects really helps us (especially database administrator), to get a better view of what’s going on within the SQL Server and databases.

We can query the dm_exec_requests management objects to get an estimate of the backup and/or restore process that are currently running in the SQL Server instance. To do that, you can use the following query:

  1: SELECT a.command,
  2:       b.text,
  3:       a.percent_complete,
  4:       a.start_time,
  5:       total_elapsed_time_minutes = 
  6:            (total_elapsed_time/1000)/60,
  7:       estimated_time_left_minutes = 
  8:            (estimated_completion_time/1000)/60,
  9:       estimated_completion_time = 
 10:            dateadd(ms, estimated_completion_time, GETDATE())
 11: FROM  sys.dm_exec_requests a
 12:       CROSS APPLY sys.dm_exec_sql_text(sql_handle) b
 13: WHERE command LIKE 'RESTORE%'
 14:       OR command LIKE 'BACKUP%'
 15: 

One thing to note is that once the backup and/or restore process is no longer running (either because it has been completed successfully or error out), you will no longer see the backup and/or restore process in the dm_exec_requests.