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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 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
-- 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...