-
Notifications
You must be signed in to change notification settings - Fork 27
/
Copy path1-Backup History
42 lines (36 loc) · 1.75 KB
/
1-Backup History
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
--TSQL Script to check latest full backup history of all databases
SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date, 108) FinishTime, x.backup_finish_date
from msdb.dbo.backupset x
JOIN ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
WHERE type = 'D'
GROUP BY a.database_name ) y on x.database_name = y.database_name
and x.backup_finish_date = y.backup_finish_date
JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id
-- where x.database_name = 'xyz'
Order by y.backup_finish_date desc
--TSQL Script to check Diff backup history of all databases
SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date, 108) FinishTime, x.backup_finish_date
from msdb.dbo.backupset x
JOIN ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
WHERE type = 'I'
GROUP BY a.database_name ) y on x.database_name = y.database_name
and x.backup_finish_date = y.backup_finish_date
JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id
-- where x.database_name = 'xyz'
Order by y.backup_finish_date desc
--TSQL Script to check Tlog backup history of all databases
SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date, 108) FinishTime, x.backup_finish_date
from msdb.dbo.backupset x
JOIN ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
WHERE type = 'L'
GROUP BY a.database_name ) y on x.database_name = y.database_name
and x.backup_finish_date = y.backup_finish_date
JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id
-- where x.database_name = 'xyz'
Order by y.backup_finish_date desc