Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DotNine SQL Server and more

I am a database specialist from the Netherland with my main focus on Microsoft SQL Server. My IT career began 14 years ago as a System Administrator. I have been working with SQL Server for more then 10 years now and a full time DBA for 5 years.

Query backup history

As a quick script tip for today I'll show you a script I use regularly for reporting purposes.
It shows you the backup history on your instance for successful backups ordered by the most recent first:

-- Get the latest successful backups
-- Shows databasename, backup start & end time, duration, backup file, 
-- backup size, compressed backup size (if used) and backup type.

SELECT
bs.database_name AS 'Database Name',
bs.backup_start_date AS 'Backup Start',
bs.backup_finish_date AS 'Backup Finished',
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS 'Duration (min)',
bmf.physical_device_name AS 'Backup File',
CASE 
WHEN bs.[type] = 'D'
THEN 'Full Backup' 
WHEN bs.[type] = 'I'
THEN 'Differential Database' 
WHEN bs.[type] = 'L'
THEN 'Log' 
WHEN bs.[type] = 'F'
THEN 'File/Filegroup' 
WHEN bs.[type] = 'G'
THEN 'Differential File'
WHEN bs.[type] = 'P'
THEN 'Partial'  
WHEN bs.[type] = 'Q'
THEN 'Differential partial' 
END
AS 'Backup Type',
ROUND(((bs.backup_size/1024)/1024),2) AS 'Backup Size (MB)',
ROUND(((bs.compressed_backup_size/1024)/1024),2) AS 'Compressed Backup Size (MB)'
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupset bs
ON bmf.media_set_id = bs.media_set_id
ORDER BY bs.backup_start_date DESC

 

Comments

Leave a comment on the original post [www.dotnine.nl, opens in a new window]

Loading comments...