Backup History

06 Jun 2022 22672 views 0 minutes to read Contributors

Show the backup history of all databases on the server.

		
			
T-SQL
			
Transact-SQL
			

			
SELECT T1.name
	,T3_full.full_backup_start_date
	,T3_full.full_backup_finish_date
	,T3_full.full_Duration
	,t3_full.full_backup_size
	,t3_full.full_physical_device_name
	,T3_diff.diff_backup_start_date
	,T3_diff.diff_backup_finish_date
	,T3_diff.diff_Duration
	,t3_diff.diff_backup_size
	,t3_diff.diff_physical_device_name
	,T3_log.log_backup_start_date
	,T3_log.log_backup_finish_date
	,T3_log.log_Duration
	,t3_log.log_backup_size
	,t3_log.log_physical_device_name
FROM master..sysdatabases T1
LEFT OUTER JOIN (
	SELECT database_name
		,MAX(full_backup_start_date) AS full_backup_start_date
		,MAX(full_backup_finish_date) AS full_backup_finish_date
		,MAX(diff_backup_start_date) AS diff_backup_start_date
		,MAX(diff_backup_finish_date) AS diff_backup_finish_date
		,MAX(log_backup_start_date) AS log_backup_start_date
		,MAX(log_backup_finish_date) AS log_backup_finish_date
	FROM (
		SELECT msdb.dbo.backupset.database_name
			,CASE 
				WHEN msdb.dbo.backupset.type = 'D'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS full_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'D'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS full_backup_finish_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'I'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS diff_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'I'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS diff_backup_finish_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'L'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS log_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'L'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS log_backup_finish_date
		FROM msdb.dbo.backupset
		GROUP BY msdb.dbo.backupset.database_name
			,msdb.dbo.backupset.type
		) max_date_subset
	GROUP BY database_name
	) T2 ON T1.name = T2.database_name
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS full_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS full_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS full_Duration
		,msdb.dbo.backupset.backup_size AS full_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS full_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
	) T3_full ON T2.database_name = T3_full.database_name
	AND t2.full_backup_start_date = T3_full.full_backup_start_date
	AND t2.full_backup_finish_date = T3_full.full_backup_finish_date
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS diff_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS diff_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS diff_Duration
		,msdb.dbo.backupset.backup_size AS diff_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS diff_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
	) T3_diff ON T2.database_name = T3_diff.database_name
	AND t2.diff_backup_start_date = T3_diff.diff_backup_start_date
	AND t2.diff_backup_finish_date = T3_diff.diff_backup_finish_date
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS log_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS log_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS log_Duration
		,msdb.dbo.backupset.backup_size AS log_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS log_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
	) T3_log ON T2.database_name = T3_log.database_name
	AND t2.log_backup_start_date = T3_log.log_backup_start_date
	AND t2.log_backup_finish_date = T3_log.log_backup_finish_date
			

				

					

				

					
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
				
						
SELECT T1.name
	,T3_full.full_backup_start_date
	,T3_full.full_backup_finish_date
	,T3_full.full_Duration
	,t3_full.full_backup_size
	,t3_full.full_physical_device_name
	,T3_diff.diff_backup_start_date
	,T3_diff.diff_backup_finish_date
	,T3_diff.diff_Duration
	,t3_diff.diff_backup_size
	,t3_diff.diff_physical_device_name
	,T3_log.log_backup_start_date
	,T3_log.log_backup_finish_date
	,T3_log.log_Duration
	,t3_log.log_backup_size
	,t3_log.log_physical_device_name
FROM master..sysdatabases T1
LEFT OUTER JOIN (
	SELECT database_name
		,MAX(full_backup_start_date) AS full_backup_start_date
		,MAX(full_backup_finish_date) AS full_backup_finish_date
		,MAX(diff_backup_start_date) AS diff_backup_start_date
		,MAX(diff_backup_finish_date) AS diff_backup_finish_date
		,MAX(log_backup_start_date) AS log_backup_start_date
		,MAX(log_backup_finish_date) AS log_backup_finish_date
	FROM (
		SELECT msdb.dbo.backupset.database_name
			,CASE 
				WHEN msdb.dbo.backupset.type = 'D'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS full_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'D'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS full_backup_finish_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'I'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS diff_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'I'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS diff_backup_finish_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'L'
					THEN MAX(msdb.dbo.backupset.backup_start_date)
				ELSE NULL
				END AS log_backup_start_date
			,CASE 
				WHEN msdb.dbo.backupset.type = 'L'
					THEN MAX(msdb.dbo.backupset.backup_finish_date)
				ELSE NULL
				END AS log_backup_finish_date
		FROM msdb.dbo.backupset
		GROUP BY msdb.dbo.backupset.database_name
			,msdb.dbo.backupset.type
		) max_date_subset
	GROUP BY database_name
	) T2 ON T1.name = T2.database_name
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS full_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS full_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS full_Duration
		,msdb.dbo.backupset.backup_size AS full_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS full_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
	) T3_full ON T2.database_name = T3_full.database_name
	AND t2.full_backup_start_date = T3_full.full_backup_start_date
	AND t2.full_backup_finish_date = T3_full.full_backup_finish_date
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS diff_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS diff_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS diff_Duration
		,msdb.dbo.backupset.backup_size AS diff_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS diff_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
	) T3_diff ON T2.database_name = T3_diff.database_name
	AND t2.diff_backup_start_date = T3_diff.diff_backup_start_date
	AND t2.diff_backup_finish_date = T3_diff.diff_backup_finish_date
LEFT OUTER JOIN (
	SELECT msdb.dbo.backupset.database_name
		,msdb.dbo.backupset.backup_start_date AS log_backup_start_date
		,msdb.dbo.backupset.backup_finish_date AS log_backup_finish_date
		,DATEDIFF(second, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS log_Duration
		,msdb.dbo.backupset.backup_size AS log_backup_size
		,msdb.dbo.backupmediafamily.physical_device_name AS log_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
	) T3_log ON T2.database_name = T3_log.database_name
	AND t2.log_backup_start_date = T3_log.log_backup_start_date
	AND t2.log_backup_finish_date = T3_log.log_backup_finish_date