SQL SERVER – Know Your Backup Before Deleting Databas

Backup Before Deleting Databas

USE MSDB
GO
SELECT
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.TYPE
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'Filegroup'
END AS backup_type,
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id =msdb.dbo.backupset.media_set_id
-- Add the WHERE condition if you want it for a specific database
-- WHERE msdb.dbo.backupset.database_name = 'AdventureWorks2014'
ORDER BY msdb.dbo.backupset.backup_finish_date