在SQL SERVER 获取当前服务器数据库备份信息,包括数据库名称,备份类型,恢复模式,时间等。 执行下面T-SQL:
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
欢迎分享,转载请注明来源:夏雨云
评论列表(0条)