Monday, June 27, 2011

SQL Server : Last Backup / Restore date of the database.

Please find below two methods to know last backup date of SQL database.

1.       In SSMS , right click on database and check the properties. Refer  Last database backup which is available under General  tab.

2.       Query : you can query msdb..backupset table.
SELECT  database_name ,
        MAX(backup_finish_date) AS backup_finish_date
FROM    msdb..backupset
WHERE   database_name = 'Database_Name'
GROUP BY database_name

Last Restore date of database Query
                SELECT  destination_database_name ,
        MAX(restore_date) AS restore_date
FROM    msdb..restorehistory
WHERE   destination_database_name = 'Database_Name'
GROUP BY destination_database_name
 
Find the all databases restore date with SourceDB , SourceFile and Backup Date

USE msdb
GO
SELECT  DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        SourceFile = physical_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

No comments:

Post a Comment