C# 备份还原SQL Server数据库

来源：互联网发布：淘宝代购服务点微信编辑：程序博客网时间：2024/06/10 18:16

        #region 备份数据信息
         /// <summary>
         ///
         /// </summary>
         /// <param name="diskStr">备份保存的磁盘</param>
         /// <param name="fileStr">文件名称</param>
         /// <param name="DataName">数据库名称</param>
        public void BackData(string diskStr,string fileStr,string DataName)
        {
            try
            {
                if (diskStr.Trim().Length > 0 && fileStr.Trim().Length>0)
                {
                    HisConnectionClass geCon = new HisConnectionClass();

                   // string strBacl = "backup database " + DataName + " to disk='" + diskStr.Trim() + "//" + fileStr.Trim() + ".bak'";
                    string strBacl = "backup database " + DataName + " to disk='" + fileStr.Trim() + ".bak'";
                //    SqlCommand Cmd = new SqlCommand(strBacl, con);
                    if (geCon.CommonExecuteNonQuery(strBacl) != 0)
                    {
                        MessageBox.Show("数据备份成功！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        geCon.CommonClose();
                    }
                    else
                    {
                        MessageBox.Show("数据备份失败！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }

                }
                else
                {
                    MessageBox.Show("请填写备份的正确位置及文件名！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }// end
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message.ToString());
            }
        }

#endregion

        #region 还原数据库操作
        /// <summary>
        /// 还原数据库操作
        /// </summary>
        /// <param name="fileStr">备份文件路径</param>
        /// <param name="DataName">需要还原的数据库的名称</param>
        public void RevertData(string fileStr,string DataName)
        {
            if (fileStr.Trim().Length>0)
            {
                SqlConnection con = new SqlConnection(new HisConnectionClass().GetConnectionSec());
                if (con.State == ConnectionState.Closed)
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                string DateStr = "Data Source=a//a;Database=master;User id=sa;PWD=";
                SqlConnection conn = new SqlConnection(DateStr);
                conn.Open();

                //-------------------杀掉所有连接 db_CSManage 数据库的进程--------------
                string strSQL = "select spid from master..sysprocesses where dbid=db_id( '" + DataName + "') ";
                SqlDataAdapter Da = new SqlDataAdapter(strSQL, conn);

DataTable spidTable = new DataTable();
Da.Fill(spidTable);

                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandType = CommandType.Text;
                Cmd.Connection = conn;

                for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
                {
                    Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();   //强行关闭用户进程
                    Cmd.ExecuteNonQuery();
                }
                conn.Close();
                conn.Dispose();
                //--------------------------------------------------------------------

                SqlConnection sqlcon = new SqlConnection(DateStr);
                sqlcon.Open();
                SqlCommand sqlCmd = new SqlCommand("backup log "+DataName+" to disk='" + fileStr.Trim() + "' restore database db_CSManage from disk='" + fileStr.Trim() + "'", sqlcon);
                sqlCmd.ExecuteNonQuery();
                sqlCmd.Dispose();
                sqlcon.Close();
                sqlcon.Dispose();
                MessageBox.Show("数据还原成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                MessageBox.Show("为了必免数据丢失，在数据库还原后将关闭整个系统。");
            //    Application.Exit();
            }
            else
            {
                MessageBox.Show("请选择备份文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

#endregion