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 :

Download Banner

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

Query to find Last Backup Timings

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.

Like what you read? Rate us