利用SQLDMO备份与还原数据库

SQLDMO.dll是随SQL Server2000一起发布的。SQLDMO.dll自身是一个COM对象

SQLDMO（SQL Distributed Management Objects，SQL分布式管理对象）封装 Microsoft SQL Server 2000 数据库中的对象。SQL-DMO 允许用支持自动化或 COM 的语言编写应用程序，以管理 SQL Server 安装的所有部分。SQL-DMO 是 SQL Server 2000 中的 SQL Server 企业管理器所使用的应用程序接口 (API)；因此使用 SQL-DMO 的应用程序可以执行 SQL Server 企业管理器执行的所有功能。
------
SQLServer的大致关系：
Application-->SQLServer-->DataBase
------
实例SQLDMO，主要用到的是其中的以下几个类：
SQLDMO.Application(使用 SQLDMO.ApplicationClass创建）、
SQLDMO.SQLServer(使用SQLDMO.SQLServerClass创建，主要用到它的Connect来连接数据库服务器）、
SQLDMO.NameList(可以通过它和Application获取服务器集合，其它的请看其API）
SQLDMO.DataBase(可以通过它和SQLServer.DataBases获取数据库集合）

个人实现的数据库备份与还原的关键代码:

---

///文件名:SqlServerBackupRestore.cs
///功能:备份与还原SqlServer数据库
///作者：杨春
///最后修改日期:2008-11-25 22:35:00
using System;
using System.Data.SqlClient;

/// <summary>
///SqlServerBackupRestore 的摘要说明
///SqlServer数据的备份与还原
/// </summary>
public class SqlServerBackupRestore:IDisposable
{
    private string _serverName = string.Empty;
    private string _userId = string.Empty;
    private string _userPassword = string.Empty;
    private string _databaseName = string.Empty;
    private string _backFile = string.Empty;
    SQLDMO.SQLServerClass server = null;
    SQLDMO.BackupClass backup = null;
    SQLDMO.RestoreClass restore = null;

    /// <summary>
    /// SqlServer备份还原
    /// </summary>
    public SqlServerBackupRestore()
    {
    }
    /// <summary>
    /// SqlServer备份还原
    /// </summary>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="backFile">备份文件名</param>
    public SqlServerBackupRestore(string connectionString, string backFile)
    {
        SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder(connectionString);
        _serverName = connectionBuilder.DataSource;
        _userId = connectionBuilder.UserID;
        _userPassword = connectionBuilder.Password;
        _databaseName = connectionBuilder.InitialCatalog;
        _backFile = backFile;
    }
    /// <summary>
    /// SqlServer备份还原
    /// </summary>
    /// <param name="serverName">服务器名或IP</param>
    /// <param name="userId">数据库帐号</param>
    /// <param name="userPassword">数据库密码</param>
    /// <param name="databaseName">数据库名</param>
    /// <param name="backFile">备份文件名</param>
    public  SqlServerBackupRestore(string serverName, string userId, string userPassword, string databaseName, string backFile)
 {
        _serverName = serverName;
        _userId = userId;
        _userPassword = userPassword;
        _databaseName = databaseName;
        _backFile = backFile;
 }
    /// <summary>
    /// 服务器名或IP
    /// </summary>
    public string ServerName
    {
        get { return _serverName; }
        set { _serverName = value; }
    }
    /// <summary>
    /// 数据库用户名
    /// </summary>
    public string UserId
    {
        get { return _userId; }
        set { _userId = value; }
    }
    /// <summary>
    /// 数据库密码
    /// </summary>
    public string Password
    {
        get { return _userPassword; }
        set { _userPassword = value; }
    }
    /// <summary>
    /// 数据库名
    /// </summary>
    public string DataBaseName
    {
        get { return _databaseName; }
        set { _databaseName = value; }
    }
    /// <summary>
    /// 备份文件名
    /// </summary>
    public string BackupFile
    {
        get { return _backFile; }
        set { _backFile = value; }
    }
    /// <summary>
    /// 备份数据库
    /// </summary>
    /// <returns>成功，true，失败，false</returns>
    public bool Backup()
    {
        bool result = true;
        server = new SQLDMO.SQLServerClass();
        backup = new SQLDMO.BackupClass();
        try
        {
            server.LoginSecure = false;
            server.Connect(ServerName, UserId, Password);
            backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
            backup.Database = DataBaseName;
            backup.Files = BackupFile;
            backup.BackupSetName = DataBaseName;
            backup.BackupSetDescription = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            backup.Initialize = true;
            backup.SQLBackup(server);
        }
        catch (Exception ex)
        {
            result = false;
            throw ex;
        }
        finally
        {
            server.DisConnect();
        }
        return result;
    }
    /// <summary>
    /// 还原数据库
    /// </summary>
    /// <returns>成功，true，失败，false</returns>
    public bool Restore()
    {
        bool result = false;
        server = new SQLDMO.SQLServerClass();
        try
        {
            server.LoginSecure = false;
            server.Connect(ServerName, UserId, Password);
            SQLDMO.QueryResults queryRestlts = server.EnumProcesses(-1);
            int iColPIDNum = -1;
            int iColDbName = -1;
            for (int i = 1; i <= queryRestlts.Columns; i++)
            {
                string strName = queryRestlts.get_ColumnName(i);
                if (strName.ToUpper().Trim() == "SPID")
                {
                    iColPIDNum = i;
                }
                else if (strName.ToUpper().Trim() == "DBNAME")
                {
                    iColDbName = i;
                }
                if (iColPIDNum != -1 && iColDbName != -1)
                    break;
            }

            for (int i = 1; i <= queryRestlts.Rows; i++)
            {
                int lPID = queryRestlts.GetColumnLong(i, iColPIDNum);
                string strDBName = queryRestlts.GetColumnString(i, iColDbName);
                if (strDBName.ToUpper() == DataBaseName.ToUpper())
                    server.KillProcess(lPID);
            }

            restore = new SQLDMO.RestoreClass();
            restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
            restore.Database = DataBaseName;
            restore.Files = BackupFile;
            restore.FileNumber = 1;
            restore.ReplaceDatabase = true;
            restore.SQLRestore(server);
            result = true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            server.DisConnect();
        }
        return result;
    }
    #region IDisposable 成员
    /// <summary>
    /// 释放资源,在用完之后显示调用
    /// </summary>
    public void Dispose()
    {
        if (server != null)
        {
            server.DisConnect();
            server = null;
        }
        if (backup != null)
        {
            backup = null;
        }
        if (restore != null)
        {
            restore = null;
        }
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

    #endregion
}

---

相关代码与DLL下载