We all know that MSSQL Server maintains all the database and other objects related information in system databases of SQL Server .
All the Backup and Restore related data for each database is maintained by SQL Server in msdb database which is one of the system database. Backup timing size of an backup is maintained in sys.backupset table of msdb .
The master database will have the information about each database properties in sys.database table.
By using the above mentioned two tables one is in master and another table is in msdb we can find the last FULL,DIFFERENTIAL,LOG backup time for each database.
Query to find Last Backup Timings :
SELECT name ,
recovery_model_desc ,
state_desc ,
d AS ‘Last Full Backup’ ,
i AS ‘Last Differential Backup’ ,
l AS ‘Last log Backup’
FROM
( SELECTdb.name ,
db.state_desc ,
db.recovery_model_desc ,
a.type,
a.backup_finish_date
FROM master.sys.databases db
LEFT JOIN msdb.dbo.backupset a ON a.database_name = db.name
) AS Sourcetable
PIVOT
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
The above query will give result like above.If no backup are taken for that database it will return NULL in Timing column.
Go questions? Email us at: vembu-support@vembu.com for answers.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
