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.

Finding System Objects in SQL Server Databases

Occasionally, mistakes were made and user database objects, such as tables, stored procedures, and functions are created in master database by accident. Now the question is how to identify the system database objects and the user database objects?

In Microsoft SQL Server Management Studio, on the Object Explorer, if you expand the database tree and then the database object tree, for example tables, you can see a folder called System Tables (for Tables), System Views (for Views), etc..

You can also query the sys.objects. There is a column in there called is_ms_shipped. It is a bit flag to identify if the “object is created by internal SQL Server component”. Please see this article to get more information on the sys.objects. So to see which objects are created by internal SQL Server components (system objects) in master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 1;

Alternatively, you can also use the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

Conversely, if you want to find user database objects in the master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 0;

Or:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 0;

There is a couple caveat to this. Kalen Delaney (blog | twitter), pointed out in her blog post, that there is an undocumented stored procedure sp_MS_marksystemobject that can marked a user created system database object into system objects. Also if we turned on the allow updates configuration in master database, then any stored procedures that the user created in the master database will be marked as 1 for the IsMSShipped property.