当前位置：文江博客话题详情

使用 SMO 查找最近的备份

发布于 2024-07-12 12:42:08 字数 31 浏览 2 评论 0原文

有谁知道如何使用 SMO 查找数据库的最新备份？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

面如桃花 2024-07-19 12:42:08

备份时间存储在表 msdb.dbo.backupset 中。下面是一个例程，它采用打开的 SQL 连接、数据库名称和指示您是否需要完整备份或任何备份的标志，并返回上次备份的时间。

请注意，此表有时会被整理，因此如果已整理，则可能表明没有备份。

   //----------------------------------------------------------------------------------------
    // Function: GetLastBackupTime
    //
    // Input
    //    sqlConnection          - An open SQLConnection to the target SQL Server
    //    DatabaseName           - Name of the database which you are interested in
    //    fullDatabaseBackupOnly - Do you want only the time of the last full backup
    //
    // Output
    //    DateTime               - DateTime.MinValue indicates no backup exists
    //                             otherwise it returns the last backup time
    //---------------------------------------------------------------------------------------

DateTime GetLastBackupTime( SqlConnection sqlConnection, 
                            string        databaseName, 
                            bool          fullDatabaseBackupOnly )
{
    DateTime lastBackupTime = DateTime.MinValue;  

    string sqlTemplate = "SELECT TOP 1 backup_finish_date " +
                         "FROM msdb.dbo.backupset " + 
                         "WHERE database_name='{0}' {1} "
                         "ORDER BY backup_finish_date DESC";

    string sql = String.Format( sqlTemplate,
                                databaseName,
                                (fullDatabaseBackupOnly ) ? " AND type='D' " : "" );

    // open connection
    using (SqlCommand cmd = new SqlCommand(sql, sqlConnection, 
    {
       object retValue = _Command.ExecuteScalar();

       if ( retValue != null ) lastBackupTime = (DateTime)retValue;
    } 

    return lastBackupTime;
}

The backup times are stored in the table msdb.dbo.backupset. Here is a routine that takes an open SQLconnection, databasename and a flag indicating whether you want full backups or any backups and returns the time of the last backup.

Note that this table sometimes gets groomed so it could indicate no backups if it has been groomed.

   //----------------------------------------------------------------------------------------
    // Function: GetLastBackupTime
    //
    // Input
    //    sqlConnection          - An open SQLConnection to the target SQL Server
    //    DatabaseName           - Name of the database which you are interested in
    //    fullDatabaseBackupOnly - Do you want only the time of the last full backup
    //
    // Output
    //    DateTime               - DateTime.MinValue indicates no backup exists
    //                             otherwise it returns the last backup time
    //---------------------------------------------------------------------------------------

DateTime GetLastBackupTime( SqlConnection sqlConnection, 
                            string        databaseName, 
                            bool          fullDatabaseBackupOnly )
{
    DateTime lastBackupTime = DateTime.MinValue;  

    string sqlTemplate = "SELECT TOP 1 backup_finish_date " +
                         "FROM msdb.dbo.backupset " + 
                         "WHERE database_name='{0}' {1} "
                         "ORDER BY backup_finish_date DESC";

    string sql = String.Format( sqlTemplate,
                                databaseName,
                                (fullDatabaseBackupOnly ) ? " AND type='D' " : "" );

    // open connection
    using (SqlCommand cmd = new SqlCommand(sql, sqlConnection, 
    {
       object retValue = _Command.ExecuteScalar();

       if ( retValue != null ) lastBackupTime = (DateTime)retValue;
    } 

    return lastBackupTime;
}

回复收藏 0 原文