Stored procedures: Who is running backups and restores and when

Determining who issued SQL Server backup and restores and when they occurred is no simple task -- unless you have these stored procedures.

This Content Component encountered an error

Backup and restore commands can be issued by running T-SQL, Enterprise Manager or Maintenance Plans. However, it's still not easy to determine who issued backups and restores -- and when.

Such information is stored in the msdb database; an entry is logged in msdb whenever a backup or restore runs. This data is not easy to access via the GUI tools, but it can be retrieved through queries. The following tables contain backup and restore information whenever one of these commands is issued.

Backup tables

  • backupfile -- contains one row for each data file or log file backed up
  • backupmediafamily -- contains one row for each media family
  • backupmediaset -- contains one row for each backup media set
  • backupset -- contains one row for each backup set

Restore tables

  • restorehistory -- contains one row for each restore run
  • restorefilegroup -- contains one row for each filegroup restored
  • restorefile -- contains one row for each physical file restored

You can query these tables individually, but to fully make sense of the data you must query the tables together. To simplify the process, the following stored procedures can be created in the msdb database or in one of your user databases. The first stored procedure queries the backup history and the second queries the restore history.

Backup History Examples
List all backups for database "master" between 7/15/2006 and 7/25/2006 dbo.uspGetDBBackupInfo '2006-07-15', '2006-07-25', 'master'
List all backups that were run between 7/15/2006 and 7/25/2006 dbo.uspGetDBBackupInfo '2006-07-15', '2006-07-25'
CREATE PROC dbo.uspGetDBBackupInfo 
           @startDate DATETIME,
           @endDate   DATETIME,
           @database  SYSNAME  = NULL
AS
  SELECT   b.database_name,
           b.backup_start_date,
           b.backup_finish_date,
           b.user_name,
           f.logical_name,
           f.physical_name,
           mf.physical_device_name,
           f.file_type,
           f.file_size,
           b.backup_size
  FROM     msdb.dbo.backupfile f,
           msdb.dbo.backupset b,
           msdb.dbo.backupmediafamily mf
  WHERE    f.backup_set_id = b.backup_set_id
           AND b.media_set_id = mf.media_set_id
           AND b.backup_start_date BETWEEN @startDate
                                           AND @endDate
           AND b.database_name = COALESCE
(@database,database_name)
  ORDER BY b.database_name,
           b.backup_start_date

Backup History Output
Output Column Description
b.database_name Name of database that was backed up
b.backup_start_date Start date and time of the backup
b.backup_finish_date End date and time of the backup
b.user_name User that ran the backup
f.logical_name Logical name of the database file
f.physical_name Physical name and location of the database file
mf.physical_device_name Name and location of the physical backup file that was created
f.file_type Type of backup D – Full, L – Transaction , I – differential
f.file_size Size of the physical database files in bytes
b.backup_size Size of the physical backup file in bytes

Restore History Examples
List all restores for database "master" between 7/15/2006 and 7/25/2006. dbo.uspGetDBBackupInfo '2006-07-15', '2006-07-25', 'master'
List all restores that were run between 7/15/2006 and 7/25/2006. dbo.uspGetDBBackupInfo '2006-07-15', '2006-07-25'
CREATE PROC dbo.uspGetDBRestoreInfo
           @startDate DATETIME,
           @endDate   DATETIME,
           @database  SYSNAME  = NULL
AS
  SELECT   h.destination_database_name,
           h.restore_date,
           h.user_name,
           h.restore_type,
           f.destination_phys_name,
           fg.filegroup_name
  FROM     msdb.dbo.restorehistory h,
           msdb.dbo.restorefile f,
           msdb.dbo.restorefilegroup fg
  WHERE    h.restore_history_id = f.restore_history_id
           AND h.restore_history_id = fg.restore_history_id
           AND h.restore_date BETWEEN @startDate
                                      AND @endDate
           AND h.destination_database_name = COALESCE
(@database,destination_database_name)
  ORDER BY h.destination_database_name,
           h.restore_date

Restore History Output
Output Column Description
h.destination_database_name Name of database the backup was restored to
h.restore_date Date and time of the restore
h.user_name User that ran the restore
h.restore_type Type of restore D – Full, L – Transaction , I – differential
f.destination_phys_name Physical name and location of the database file
fg.filegroup_name Name of the filegroup that was restored

These two simple, but effective stored procedures can help shed light on when backups and restores are being issued against your server. You can run the stored procedures every day, so you can be sure backups are running properly. Also, when you need to restore, use the history to see the order of the backups and which backup files exist and where they are physically located.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the Backup and Recovery expert welcomes your questions.

This was first published in July 2006

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close