SQL SERVER查看数据库备份信息

最新推荐文章于 2024-08-14 11:17:08 发布

Perry705

最新推荐文章于 2024-08-14 11:17:08 发布

阅读量1.9k

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/u011616400/article/details/46437585

版权

SQL Server 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--Last Full, Diff, and Log backup taken with recovery model
--Get the most recent Full backup taken 

SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Full' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'D' 
GROUP BY T1.Name 

--Get the most recent Diff backup taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Differential' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'I' 
GROUP BY T1.Name 

--Get the most recent Log backup taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'Log' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type = 'L' 
GROUP BY T1.Name 

--Get the databases with no backup yet taken 
UNION ALL 
SELECT T1.Name AS DatabaseName, 
 Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model, 
 'No Backup' AS BackupType, 
 Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date), 131)), '') AS LastBackUpTaken 
FROM sys.sysdatabases T1 
 LEFT OUTER JOIN msdb.dbo.backupset T2 
 ON T2.database_name = T1.name 
WHERE type IS NULL 
GROUP BY T1.Name 

--Sort the combined results
ORDER BY T1.name, 
 BackupType