By this SQL you can find the backup DB name, date-time when the backup was taken, media to use, backup-size, logical path to take backup and many more...
By this SQL you also can find the location where to take backup last time ...
Below Query will show the result for lastest 10 days history. If you need to number of the day of history to check, please change in WHERE condition...
| SELECT CONVERT(CHAR(100), Serverproperty('Servername')) AS Server, bkset.database_name, bkset.backup_start_date, bkset.backup_finish_date, bkset.expiration_date, CASE bkset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, bkset.backup_size, bkf.logical_device_name, bkf.physical_device_name, bkset.NAME AS backupset_name, bkset.description, * FROM msdb.dbo.backupmediafamily bkf INNER JOIN msdb.dbo.backupset bkset ON bkf.media_set_id = bkset.media_set_id WHERE ( CONVERT(DATETIME, bkset.backup_start_date, 102) >= Getdate() - 10 ) ORDER BY bkset.database_name, bkset.backup_finish_date |
This SQL will give output as below .. due to some restriction, I am blackout some data ...
 |
| Sample Output of the above SQL |
