Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL List Database or Log Backups
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-06-03









T-SQL:


DECLARE @DBName SYSNAME;
DECLARE @BackupType char(1) = 'L' --L = Log Backup, D = Database Backup
SET @DBName = DB_NAME();

SELECT
ServerName = bs.server_name
, DBName = bs.database_name
, BackupType = CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' ELSE bs.type END
, BackupStartDate = bs.backup_start_date
, BackupEndDate = bs.backup_finish_date
, BackupDurationSec = DATEDIFF(ss,bs.backup_start_date,bs.backup_finish_date)
, DBRecoveryModel = bs.recovery_model
, PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE (bs.database_name = @DBName OR @DBName IS NULL) AND bs.type = @BackupType
ORDER BY bs.backup_start_date DESC;