获取当前服务器数据库备份信息代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】获取当前服务器数据库备份信息

作者:dezai / 发布于2014/2/8/ 842

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

试试其它关键字

　数据库备份信息　

同语言下

可能有用的

dezai贡献的其它代码(1065)