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.
- 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
- 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|
|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|
|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.