winform程序备份和还原数据库

Question

小妹做了一个winform系统，需要对sql2000数据库备份和还原
备份的代码如下：
 /// <summary>
        /// 数据库备份
        /// </summary>
        public static int DbBackup(string serverip, string username, string psw, string path)
        {
            int ret;
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(serverip, username, psw);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = "BookLibrarySystem";
                oBackup.Files = path + "\\BookLibrarySystem.bak"; ;
                oBackup.BackupSetName = "BookLibrarySystem";
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                ret = 1;
            }
            catch
            {
                ret = 0;
                throw;            }
            finally
            {
                oSQLServer.DisConnect();
            }
            return ret;
        }
这样可以备份成功，生成文件BookLibrarySystem.bak
但是数据库还原的，我需要登录到我的系统后，点某个按钮还原，我搜了资料，说要杀死所有进程才能还原，但是我的系统已经链接到数据库了，这样就会报数据库被使用，不能杀死进程这种类似的错误，请各位路过的都来看一看，代码如下：
 /// <summary>
        /// 数据库恢复
        /// </summary>
        public static int DbRestore(string serverip, string username, string psw, string path)
        {
            int ret;
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                exepro(serverip, username, psw);
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(serverip, username, psw);
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = "BookLibrarySystem";
                oRestore.Files = path + "\\BookLibrarySystem.bak";
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
                ret = 1;
            }
            catch
            {
                ret = 0;
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
            return ret;
        }        /**/
        /// <summary>
        /// 杀死当前库的所有进程
        /// </summary>
        /// <returns></returns>
        private static bool exepro(string serverip, string username, string psw)
        {            SqlConnection conn1 = new SqlConnection("server=" + serverip + ";uid=" + username + ";pwd=" + psw + ";database=master");
            SqlCommand cmd = new SqlCommand("p_killspid", conn1);
            cmd.Parameters.Add(new SqlParameter("@dbname", "BookLibrarySystem"));
            try
            {
                conn1.Open();
                SqlDataReader dr = cmd.ExecuteReader();                ArrayList list = new ArrayList();
                while (dr.Read())
                {
                    list.Add(dr.GetInt16(0));                }
                dr.Close();
                for (int i = 0; i < list.Count; i++)
                {
                    cmd = new SqlCommand(string.Format("KILL {0}", list), conn1);
                    cmd.ExecuteNonQuery();
                }
                conn1.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn1.Close();
            }
        }

Answer 1

没做过 但是可以用 processor 调用cmd.exe  运行dts来执行

Answer 2

  你要还原数据库是不能让软件和数据库有链接的，这是肯定的啊，你只有在断开状态下才能还原数据库。你看 执行Dos 命令试试 行不行

Answer 3

http://www.enet.com.cn/article/2007/1112/A20071112906795.shtml
看看这里啦，不用kill进程的

Answer 4

http://www.softhouse.com.cn/news/show/691.html
你还可以看看这个，用了SQLDMO封装类备份还原，方便、快捷、减少自己的编码量！！！
强烈推荐！！！

Answer 5

成功了，把代码帖出来给
大家看看，跟我刚开始帖出来的差不多，是需要杀掉进程的，我之前调用的存储过程，可能有点问题，现在改成sql了
/// <summary>
        /// 数据库备份
        /// </summary>
        public static int DbBackup(string serverip, string username, string psw, string path)
        {
            int ret;
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(serverip, username, psw);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = "BookLibrarySystem";
                oBackup.Files = path + "\\BookLibrarySystem.bak"; ;
                oBackup.BackupSetName = "BookLibrarySystem";
                oBackup.BackupSetDescription = "数据库备份";
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
                ret = 1;
            }
            catch
            {
                ret = 0;
                throw;            }
            finally
            {
                oSQLServer.DisConnect();
            }
            return ret;
        }        /// <summary>
        /// 数据库恢复
        /// </summary>
        public static int DbRestore(string serverip, string username, string psw, string path)
        {
            int ret;
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                exepro(serverip, username, psw);
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(serverip, username, psw);
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = "BookLibrarySystem";
                oRestore.Files = path + "\\BookLibrarySystem.bak";
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
                ret = 1;
            }
            catch
            {
                ret = 0;
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
            return ret;
        }        /**/
        /// <summary>
        /// 杀死当前库的所有进程
        /// </summary>
        /// <returns></returns>
        private static bool exepro(string serverip, string username, string psw)
        {            SqlConnection conn1 = new SqlConnection("server=" + serverip + ";uid=" + username + ";pwd=" + psw + ";database=master");            string cmdTxt = "use master;";
            cmdTxt += " declare  @sql nvarchar(500) declare @spid int set @sql='declare getspid  cursor for ";
            cmdTxt += " select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) ";
            cmdTxt += " open getspid fetch next from getspid into @spid  while @@fetch_status <>-1 begin ";
            cmdTxt += " exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid ";            //SqlCommand cmd = new SqlCommand("p_killspid", conn1);
            SqlCommand cmd = new SqlCommand(cmdTxt, conn1);            cmd.Parameters.Add(new SqlParameter("@dbname", "BookLibrarySystem"));
            cmd.CommandType = CommandType.Text;            try
            {
                conn1.Open();
                SqlDataReader dr = cmd.ExecuteReader();                ArrayList list = new ArrayList();
                while (dr.Read())
                {
                    list.Add(dr.GetInt16(0));                }
                dr.Close();
                for (int i = 0; i < list.Count; i++)
                {
                    cmd = new SqlCommand(string.Format("KILL {0}", list), conn1);
                    cmd.ExecuteNonQuery();
                }
                conn1.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn1.Close();
            }
        }

Answer 6

也可使用存储过程备份恢复数据库
CREATE proc dbo.Data_Backup  @dbname sysname='',@bkpath nvarchar(260)='',@bkfname nvarchar(260)='',@bktype nvarchar(10)='DB',@appendfile bit=1 
as declare @sql varchar(8000)  if isnull(@dbname,'')='' set @dbname=db_name()  if isnull(@bkfname,'')='' 
set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'  set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname),'\DATE\',convert(varchar,getdate(),112)),'\TIME\',replace(convert(varchar,getdate(),108),':','')) 
update XTSZ set FNAME=@bkfname
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname +' to disk='''+@bkpath+@bkfname +''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end+case @appendfile when 1 then 'NOINIT' else 'INIT' end 
exec(@sql)