SQL SERVER获取当前服务器
数据库备份信息
在SQL SERVER 获取当前服务器
数据库备份信息，包括数据库名称，备份类型，恢复模式，时间等。 执行下面T-SQL：
--Last Full, Diff, and Log backup taken with recovery model
--Get the most recent Full backup taken
www.2cto.com
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 www.2cto.com
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
下面以SQL SERVER 2008 R2 环境下，输出的示例结果集是：
希望对您数据库开发有帮助。
作者 Petter Liu
