SQL SERVER 获取当前服务器数据库备份信息
在SQL SERVER 获取当前服务器数据库备份信息，包括数据库名称，备份类型，恢复模式，时间等。 执行下面T-SQL：
--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 resultsORDER BY T1.name, BackupType
下面以SQL SERVER 2008 R2 环境下，输出的示例结果集是：
| DatabaseName | recovery_model | BackupType | LastBackUpTaken |
| AdventureWorks | No Backup Taken | No Backup | |
| AdventureWorks2008R2 | No Backup Taken | No Backup | |
| ef4prs | FULL | Full | May 28 2012 4:49PM |
| master | No Backup Taken | No Backup | |
| model | No Backup Taken | No Backup | |
| msdb | No Backup Taken | No Backup | |
| OneDir | FULL | Differential | May 28 2012 4:50PM |
| OneDir | FULL | Full | May 28 2012 4:50PM |
| ReportServer | No Backup Taken | No Backup | |
| ReportServerTempDB | No Backup Taken | No Backup | |
| tempdb | No Backup Taken | No Backup | |
| TestDb | No Backup Taken | No Backup |
希望对您数据库开发有帮助。
您可能感兴趣的文章：
实用T-SQL之生成当前索引数据库中的外键上
SQL Server简洁查询正在运行的进程
SQLSERVER2008中CTE的Split与CLR的性能比较
作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。
TAG:
