Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Advanced
May 11, 2016

Find out When the Last Full Backup was Taken

The following query shows the last time a full backup was taken on a certain database.

select database_name as [Database], a.name [NameOfBackup], user_name as [BackupTakenBy],
backup_start_date, backup_finish_date, backup_size as [SizeOfBackup], is_copy_only,
has_backup_checksums, b.physical_device_name as [LocationOfBackup]
from msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b on a.media_set_id=b.media_set_id
where backup_set_id in
(select max(backup_set_id) from msdb.dbo.backupset where type='D' and is_copy_only in (0)
group by database_name) 

This displays the last time a full backup was taken, as well as the user who took the last backup.

Hannes du Preez
Thanks for your registration, follow us on our social networks to keep up-to-date