关于C#中如何实现数据还原和数据备份

Question

如何在C#中通过代码实现数据库即时数据备份和数据库还原~
请大虾指教~~

Answer 1

备份和还原对C#代码来说 和 "SELECT * FROM Table1 " 一样,都是普通的字符串.
把备份和还原的Sql语句用ExcuteNonQuery()执行就OK了, 注意执行权限.

Answer 2

要是想备份和还原数据库,你上网找一下,很多主要是利用SQL自身还原备份命令实现!

Answer 3

private void BackUpSQLServer()
        {
            string str = strPath + "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
            string strConnectstring = "Server=" + this.textBox2.Text.Trim() + ";Database=Master;User ID=" + this.textBox4.Text.Trim() + ";Password=" + textBox4.Text.Trim() + ";";
            SqlConnection conn = new SqlConnection(strConnectstring);            SqlCommand cmdBK = new SqlCommand();
            cmdBK.CommandType = CommandType.Text;
            cmdBK.Connection = conn;
            cmdBK.CommandText = @"backup database " + textBox3.Text.Trim() + " to disk='" + str + "' with init";            try
            {
                conn.Open();
                cmdBK.ExecuteNonQuery();
                this.richTextBox1.Text = "备份成功\n文件路径为：" + str + "\n操作时间：\n" + DateTime.Now.ToString();                this.richTextBox1.ForeColor = Color.Blue;
            }
            catch (Exception ex)
            {
                this.richTextBox1.Text = "备份失败\n失败原因\n" + ex.ToString() + "\n操作时间\n" + DateTime.Now.ToString();
                this.richTextBox1.ForeColor = Color.Red;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }        private void RestoreSQLServer()
        {
            string strConnectstring = "Server=" + this.textBox2.Text.Trim() + ";Database=Master;User ID=" + this.textBox4.Text.Trim() + ";Password=" + textBox5.Text.Trim() + ";";
            SqlConnection conn = new SqlConnection(strConnectstring);
            conn.Open();            //KILL DataBase Process 
            SqlCommand cmd = new SqlCommand("SELECT spid FROM dbo.sysprocesses ,dbo.sysdatabases WHERE dbo.sysprocesses.dbid=dbo.sysdatabases.dbid AND dbo.sysdatabases.Name='" + textBox3.Text.Trim() + "'", conn);
            SqlDataReader dr;
            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[i]), conn);
                cmd.ExecuteNonQuery();
            }            SqlCommand cmdRT = new SqlCommand();
            cmdRT.CommandType = CommandType.Text;
            cmdRT.Connection = conn;
            cmdRT.CommandText = @"restore database " + textBox3.Text.Trim() + "  from disk='" + strPath + "'";            try
            {
                cmdRT.ExecuteNonQuery();
                this.richTextBox1.Text = "还原成功\n文件路径为：" + strPath + "\n操作时间：\n" + DateTime.Now.ToString();
                this.richTextBox1.ForeColor = Color.Blue;
            }
            catch (Exception ex)
            {
                this.richTextBox1.Text = "还原失败\n失败原因\n" + ex.ToString() + "\n操作时间：\n" + DateTime.Now.ToString();
                this.richTextBox1.ForeColor = Color.Red;
            }
            finally
            {
                conn.Close();
            } 
        }里面的部分内容自己修改一下，就OK了

Answer 4

那有没有可以调用执行SQL脚本的方法或者类库~刚学C# 了解不时很多
还请指点~~

Answer 5

  
SQL 语句的方法public string BackUpDB(string WebName, string DbName)
        {
            string ErrMessage=string.Empty ;
            string db_backname = @"E:\SQLBACK\" + DbName.Trim() ;         
   if (File.Exists(db_backname))
            {
                return "经备份过了";
            }
  string sql1 = @"backup database  " + DbName + " to disk='" + db_backname + "'";
            
            DBAction dbAction=new DBAction();
            ErrMessage=dbAction.ExcuteCommand(sql1);
            if( ErrMessage !="OK")
                {
                    return  ErrMessage;
                }
                else
                {
                    return "OK";
                }
               }
            else
            {
                return ErrMessage;
            }        }
  public string ExcuteCommand(string Command)
        {
            SqlCommand cmd = new SqlCommand(Command, con);            try
            {
                con.Open();                int i = cmd.ExecuteNonQuery();                con.Close();                if (i > 0)
                {
                    return "OK";                }
                else
                {
                    return "NoRows";
                 }              }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

Answer 6

6楼的没有问题,可以成功.
备份成功文件路径为： E:\Projects\WebSite\AspNet_Site\QQNumberCurrent\DataBack\2007112111337859.bak  
操作时间： 2007-11-21 1:13:41

Answer 7

    //备份
    private void Back()
    {
        string strCn = "你的数据库连接字符串";
        SqlConnection sqlCn = new SqlConnection(strCn);
        SqlCommand sqlCmd = new SqlCommand("backup   database 你要备份的数据库名 to disk=备份要存储的路径 ", sqlCn);
        sqlCmd.CommandType = CommandType.Text;
        try
        {
            sqlCn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCn.Close();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        { 
        }
    }
    //还原
    private void Back()
    {
        string strCn = "你的数据库连接字符串";
        SqlConnection sqlCn = new SqlConnection(strCn);
        SqlCommand sqlCmd = new SqlCommand("restore   database 你要还原的数据库名 from disk=备份存储的路径 ", sqlCn);
        sqlCmd.CommandType = CommandType.Text;
        try
        {
            sqlCn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCn.Close();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
        }
    }

Answer 8

上面笔误  //备份
    private void Back()
    {
        string strCn = "你的数据库连接字符串";
        SqlConnection sqlCn = new SqlConnection(strCn);
        SqlCommand sqlCmd = new SqlCommand("backup   database 你要备份的数据库名 to disk=备份要存储的路径 ", sqlCn);
        sqlCmd.CommandType = CommandType.Text;
        try
        {
            sqlCn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCn.Close();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        { 
        }
    }
    //还原
    private void restore()
    {
        string strCn = "你的数据库连接字符串";
        SqlConnection sqlCn = new SqlConnection(strCn);
        SqlCommand sqlCmd = new SqlCommand("restore   database 你要还原的数据库名 from disk=备份存储的路径 ", sqlCn);
        sqlCmd.CommandType = CommandType.Text;
        try
        {
            sqlCn.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCn.Close();
        }
        catch (Exception ex)
        {
            throw (ex);
        }
        finally
        {
        }
    }