如何使用sqldmo备份还原数据库

[wanghaopeng]

这是备份的代码：

if (MessageBox.Show("您要备份数据库吗？", "备份数据库", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {

                SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
                SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
                try
                {
                    oSQLServer.LoginSecure = false;
                    oSQLServer.Connect("PC201012291333", "sa", "");//PC201012291333是我的机器名，sa没有设置密码
                    oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                    oBackup.Database = "db_mhjg";//这是我的数据库名
                    oBackup.Files = @"C:\Documents and Settings\Administrator\桌面\123.bak";//这是我的备份文件路径和名称
                    oBackup.BackupSetName = "db_mhjg";
                    oBackup.BackupSetDescription = "数据库备份";
                    oBackup.Initialize = true;
                    oBackup.SQLBackup(oSQLServer);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    oSQLServer.DisConnect();
                }

总是报错无法连接上，为什么呢？

下面这个是还原的代码：

                SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
                try
                {
                    svr.Connect("PC201012291333", "sa", "");
                    SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                    int iColPIDNum = -1;
                    int iColDbName = -1;
                    for (int i = 1; i <= qr.Columns; i++)
                    {
                        string strName = qr.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 <= qr.Rows; i++)
                    {
                        int lPID = qr.GetColumnLong(i, iColPIDNum);
                        string strDBName = qr.GetColumnString(i, iColDbName);
                        if (strDBName.ToUpper() == strDbName.ToUpper())
                            svr.KillProcess(lPID);
                    }


                    SQLDMO.Restore res = new SQLDMO.RestoreClass();
                    res.Action = 0;

                    res.Files = @"C:\Documents and Settings\Administrator\桌面\123.bak";//123.bak是已经被分好的数据库名字

                    res.Database = db_mhjg;//要备份的数据库
                    res.ReplaceDatabase = true;
                    res.SQLRestore(svr);
                    return true;
                }
                catch (Exception err)
                {
                    throw (new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message));
                    //return false ; 
                    //MessageBox.Show("恢复数据库失败,请关闭所有和该数据库连接的程序！"+err.Message);
                }
                finally
                {
                    svr.DisConnect();
                }

这两段代码总是不能执行，请各位大侠帮忙解决一下，看我哪里写错了，谢谢啦

[wanghaopeng]

我是sql server2005，用sql server management studio连接没有一点问题。而且我的数据库已经设置允许远程访问，并且防火墙都关掉了还是不行？到底是哪里的错呢？

[蝶恋花雨]

用代码查询一个表的信息这样看看。到底是不是链接字符串的问题。
http://wenku.baidu.com/view/84310008763231126edb11fa.html
您是否在使用SQL Server 2005 Express 请在“Server”选项使用连接表达式“主机名称\SQLEXPRESS”。

[wanghaopeng]

备份的可以了，还原的还不行，kongwei521，能否给我贴一下带杀死所有进程的还原数据库的代码？谢谢啦

[蝶恋花雨]

http://blog.csdn.net/jinqinghai/archive/2007/05/10/1603382.aspx
http://www.cnblogs.com/mbskys/articles/650364.html
http://www.cnblogs.com/Terrylee/archive/2005/10/13/253667.html
去看看吧。里面有

[蝶恋花雨]

 #region 还原数据库
        /// <summary>
        /// 恢复数据库，恢复前杀死所有与本数据库相关进程
        /// </summary>
        /// <param name="strDbName">数据库名</param>
        /// <param name="strFileName">存放路径</param>
        /// <param name="pgbMain"></param>
        /// <returns></returns>
        public bool RestoreDB(string strDbName, string strFileName)
        {
            SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
            try
            {
                svr.Connect(ServerName, UserName, Password);
                //取得所有的进程列表
                SQLDMO.QueryResults qr = svr.EnumProcesses(-1);
                int iColPIDNum = -1;
                int iColDbName = -1;
                //找到和要恢复数据库相关的进程
                for (int i = 1; i <= qr.Columns; i++)
                {
                    string strName = qr.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 <= qr.Rows; i++)
                {
                    int lPID = qr.GetColumnLong(i, iColPIDNum);
                    string strDBName = qr.GetColumnString(i, iColDbName);
                    if (strDBName.ToUpper() == strDbName.ToUpper())
                        svr.KillProcess(lPID);
                }

                SQLDMO.Restore res = new SQLDMO.RestoreClass();

                res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                res.Files = strFileName;

                res.Database = strDbName;
                res.FileNumber = 1;
                
                res.ReplaceDatabase = true;
                res.SQLRestore(svr);
                
                return true;
            }
            catch (Exception err)
            {
                //throw (new Exception("" + err.Message));
                ShowError("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message);
                return false;
            }
            finally
            {
                svr.DisConnect();
            }
        }
        #endregion

[wanghaopeng]

问题解决，万分感谢kongwei521!!结贴给分

[蝶恋花雨]

备份
//引用SQLDMO.dll，SQLDMO由Microsoft   SQL   Server自带的SQLDMO.dll提供，SQLDMO.dll是一个COM对象 
SQLDMO.Backup   backup=new   SQLDMO.BackupClass(); 
SQLDMO.SQLServer   sqlserver=new   SQLDMO.SQLServerClass(); 
sqlserver.LoginSecure=false; 
sqlserver.Connect( "localhost ", "sa ", "1 "); 
backup.Action=SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; 
backup.Database= "userdb "; 
backup.Files=@ "E:\Project\meng\data\userdb.bak "; 
backup.BackupSetName= "userdb "; 
backup.BackupSetDescription= "数据库备份 "; 
backup.Initialize=true; 
backup.SQLBackup(sqlserver); 
} 

//还原
SQLDMO.Restore   restore=new   SQLDMO.RestoreClass(); 
SQLDMO.SQLServer   sqlserver=new   SQLDMO.SQLServerClass(); 
sqlserver.LoginSecure=false; 
sqlserver.Connect( "192.168.19.25 ", "sa ", "sa "); restore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; 
restore.Database= "userdb "; 
restore.Files=@ "E:\Project\meng\data\userdb.bak "; 
restore.BackupSetName= "userdb "; 
restore.FileNumber=1; 
restore.SQLRestore(sqlserver);

[wanghaopeng]

写的和我的基本一样啊，还是不行呢，我估计就是oSQLServer.Connect("PC201012291333", "sa", "")这句的错，这三个参数应该怎么设置呢？

[蝶恋花雨]

你先用企业管理器连连你数据库能打开吗？
oSQLServer.Connect("数据库名称", "用户名", "密码")