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

代码语言

代码分类

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

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

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)