Wednesday, May 4, 2011

SQL Server Backup and Restore History

Hi All.. This is one place I know most you must have knowledge about. Just mentioning this so that you all have one stop for the information.

To identify the backup history for the databases on the server.

SELECT, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON = b.database_name
GROUP BY, b.type
ORDER BY, b.type

To identify the restore history of the databases on the server.
select restore_history_id,restore_date,destination_database_name,
user_name,restore_type,backup_set_id from msdb..restorehistory


No comments:

Post a Comment