数据库备份与还原

数据库远程备份与还原
1、Web.config
 <connectionStrings>
    <add name="BeiFenConnectionString" connectionString="Data Source=192.168.1.201;Initial Catalog=SchoolDB;User ID=huang;Password=123456;"/>
    
    <add name="HuanYuanConnectionStringRestore" connectionString="Data Source=192.168.1.201;Initial Catalog=master;User ID=huang;Password=123456;"/>
  </connectionStrings>
 <appSettings>
  <!--数据库备份文件存放地址-->
  <add key="DBBack" value="E:\database\SchoolDB.bak" />
 </appSettings>
2、aspx
<asp:Button ID="Button1" runat="server" Text="备份" OnClick="Button1_Click" />
    <asp:Button ID="Button2" runat="server" Text="还原" OnClick="Button2_Click" />
3、aspx.cs
 //数据库备份
        protected void Button1_Click(object sender, EventArgs e)
        {
            string con_str = ConfigurationManager.ConnectionStrings["BeiFenConnectionString"].ToString();
            SqlConnection conn = new SqlConnection(con_str);
            SqlCommand cmdBK = new SqlCommand();
            cmdBK.CommandType = CommandType.Text;
            cmdBK.Connection = conn;
            cmdBK.CommandText = @"backup database SchoolDB to disk='E:\Backup\SchoolDB" + ".bak'" + " with init";
            try
            {
                conn.Open();
                cmdBK.ExecuteNonQuery();
                System.Web.HttpContext.Current.Response.Write("<Script>alert('数据库备份或更新备份成功!');</Script>");
            }
            catch (Exception)
            {
                System.Web.HttpContext.Current.Response.Write("<Script>alert('抱歉,服务器连接失败!或者要备份的数据库不存在于服务器中,请先尝试新建或还原!');</Script>");
            }
            finally
            {
                conn.Close();
            }
        }
 
  //还原数据库
        protected void Button2_Click(object sender, EventArgs e)
        {
            string con_str = ConfigurationManager.ConnectionStrings["HuanYuanConnectionStringRestore"].ToString();
            SqlConnection conn = new SqlConnection(con_str);
            try
            {
                conn.Open();
                SqlCommand cmdRT = new SqlCommand();
                cmdRT.CommandType = CommandType.Text;
                cmdRT.Connection = conn;
                cmdRT.CommandText = "select * from sys.databases where name='SchoolDB'";
                object isHave = cmdRT.ExecuteScalar();
                if (isHave == null)//不存在
                {
                    cmdRT.CommandText = @"restore database SchoolDB from disk= 'E:\Backup\SchoolTest20120808" + ".bak'";
                    cmdRT.ExecuteNonQuery();
                    System.Web.HttpContext.Current.Response.Write("<Script>alert('数据库还原成功!');</Script>");
                }
                else
                {
                    System.Web.HttpContext.Current.Response.Write("<Script>alert('此数据库已存在!');</Script>");
                }
            }
            catch (Exception)
            {
                System.Web.HttpContext.Current.Response.Write("<Script>alert('抱歉,服务器连接失败!或者要还原的数据库备份不存在于服务器中,请先尝试备份;或者数据库目前运转正常而不必还原!');</Script>");
            }
            finally
            {
                conn.Close();
            }
----------------------------------------------------------------------------------------------
还原数据库方案二------------【直接覆盖同名数据库】
protected void Button2_Click(object sender, EventArgs e)
        {
            string con_str = ConfigurationManager.ConnectionStrings["HuanYuanConnectionStringRestore"].ToString();
            SqlConnection conn = new SqlConnection(con_str);
            try
            {
                conn.Open();
                //杀死所有正在使用要还原数据的数据库进程
                SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='aaa'", conn);
                SqlDataReader dr;
                dr = cmd.ExecuteReader();
                ArrayList list = new ArrayList();
                while (dr.Read())
                {
                    list.Add(dr.GetInt16(0));
                }
                dr.Close();
                for (int k = 0; k < list.Count; k++)
                {
                    cmd = new SqlCommand(string.Format("KILL {0}", list[k]), conn);
                    cmd.ExecuteNonQuery();
                }

                SqlCommand cmdRT = new SqlCommand();
                cmdRT.CommandType = CommandType.Text;
                cmdRT.Connection = conn;
                cmdRT.CommandText = @"restore database SchoolDB from disk= 'E:\Backup\SchoolTest20120808.bak' with replace ";//若存在 则直接覆盖
                cmdRT.ExecuteNonQuery();
                System.Web.HttpContext.Current.Response.Write("<Script>alert('数据库还原成功!');</Script>");
            }
            catch (Exception)
            {
                System.Web.HttpContext.Current.Response.Write("<Script>alert('抱歉,服务器连接失败!或者要还原的数据库备份不存在于服务器中,请先尝试备份;或者数据库目前运转正常而不必还原!');</Script>");
            }
            finally
            {
                conn.Close();
            }
        }