Query to find when last full backup was taken

Backup 1 Minute

The below query returns last time a full backup was taken on the database.

select database_name as [Database],a.name [BackupName],user_name as [BackupTakenBy],
backup_start_date,backup_finish_date,backup_size as [BackupSize],is_copy_only,
has_backup_checksums,b.physical_device_name as [BackupLocation]
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 (1,0)
group by database_name)

If you do not want to consider copy_only backups, replace “is_copy_only in (1,0)” with “is_copy_only in (0)”.

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published