Backup and restore Audit in SQL Server

by dotnetpete 17. December 2012 17:46

Found a very cool way to check backup and restore history of SQL Server databases. MSDB holds a host of usually irrelevant information until you really need it that is.

Here's the statment:

SELECT [rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC


Add comment