Gonzo TechNet
SQL Maintenance tips & tricks

MSDB Utility Queries

Each time you execute the T-SQL BACKUP command on a MSSQL Server, the system automatically records a plethora of information. This includes the start/end time of the backup, the backup type (Log/Data), the file/device the backup was written to, etc., etc.

List the backup history for a specific database
-- -- List the last 100 Backup HISTORY records for a specific database -- USE msdb GO DECLARE @dbname varchar(100) -- SET @dbname = 'MyDBName' --<== SET Target DBName HERE!!! -- SELECT TOP 100 @@servername as Servername ,bs.database_name as DBName ,databasepropertyex(bs.database_name,'Recovery') AS RecoveryModel ,case when bs.type = 'D' THEN 'Database' WHEN bs.type = 'L' THEN 'Log' END AS BackupType ,bs2.backup_start_date ,bs2.backup_finish_date ,datediff(mi,bs2.backup_start_date,bs2.backup_finish_date) as Duration ,bmf.physical_device_name FROM dbo.backupset as bs JOIN msdb.dbo.backupset as bs2 ON (bs.backup_set_id = bs2.backup_set_id) JOIN msdb.dbo.backupmediafamily as bmf on (bs2.media_set_id = bmf.media_set_id) WHERE bs.database_name like @dbname ORDER BY bs2.backup_start_date DESC
Home | TechNet | ADO.Net | DOS | ASP.NET | IIS | VB.NET | VIM (vi) | Windows | XHtml
MS-SQL | T-SQL | SSIS | Oracle