 |
|
| SQL Server Tips: |
|
|
|
MICROSOFT SQL SERVER
Stored procedures: Who is running backups and restores and when
Greg Robidoux, Edgewood Solutions
07.27.2006
Rating: -4.75- (out of 5)
|
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.
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
