Wednesday, October 15, 2014

Database backup and restore history

Here is the script that will help you the database backup and restore histories

SELECT TOP 10 b.database_name
, BMF.physical_device_name
as BackupFileName
, B.backup_finish_date
as BackupDate
, B.backup_size
/1024.0/1024.0 AS BackupSizeMB
FROM
msdb.dbo.backupset B
JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
--WHERE database_name = '<YourDatabaseName>'
ORDER BY B.backup_finish_date DESC


Restore history



SELECT TOP 10
RH.destination_database_name
AS [Database],
RH.
user_name AS [Restored By],
RH.restore_date
AS [Restore Started],
BMF.physical_device_name
AS [Restored From],
RF.destination_phys_name
AS [Restored To],
RH.
*
FROM
msdb.dbo.restorehistory RH
INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DES

0 comments:

Post a Comment