C#备份、恢复数据库

C#中备份与恢复数据库

1. 界面设计
   

   界面中控件名称有：
   txtBackup：备份路径
   txtRestore：恢复路径
   btnBackup：开始备份按钮
   btnRestore：开始恢复按钮
   sdlgBackup：数据备份文件对话框
   odlgRestore：数据恢复文件对话框
   btnBackupFile：打开文件备份对话框按钮
   btnRestoreFile：打开文件恢复对话框按钮

2. 备份

    //打开文件备份按钮事件
       private void btnBackupFile_Click(object sender, EventArgs e)
       {
           sdlgBackup.FilterIndex = 0;
           sdlgBackup.FileName = "";
           sdlgBackup.Filter = "Bak Files (*.bak)|*.bak|All Files (*.*)|*.*";
           if (sdlgBackup.ShowDialog() == DialogResult.OK)
           {
               txtBackup.Text = sdlgBackup.FileName.ToString();
               txtBackup.ReadOnly = true;
           }
           backupPath = txtBackup.Text.Trim();
       }
   
       //开始备份
       private void btnBackup_Click(object sender, EventArgs e)
       {
           try
           {
               if (backupPath == "")
               { 
                   MessageBox.Show("请先选择数据库备份路劲","提示");
                   return;
               }
               if(File.Exists(backupPath))
               {
                   File.Delete(backupPath);
               }
               string sqlStr;
               sqlStr = "backup database 数据库名 to disk='" + backupPath + "'";
               SqlCommand sqlCom = new SqlCommand(sqlStr ,CDataBase.conn);
               CDataBase.conn.Open();
               sqlCom.ExecuteNonQuery();
               CDataBase.conn.Close();
               if(MessageBox.Show("数据库备份成功","提示",MessageBoxButtons.OK) == DialogResult.OK)
               {
                   this.Close();
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
           finally
           {
               CDataBase.conn.Close();
           }
       }

3. 恢复

//打开恢复文件对话框按钮
private void btnRestoreFile_Click(object sender, EventArgs e)
        {
            odlgRestore.FilterIndex = 0;
            odlgRestore.FileName = "";
            odlgRestore.Filter = "Bak Files (*.bak)|*.bak|All Files(*.*)|*.*";
            if (odlgRestore.ShowDialog() == DialogResult.OK)
            {
                txtRestore.Text = odlgRestore.FileName.ToString();
                txtRestore.ReadOnly = true;
            }
            restorePath = txtRestore.Text.Trim();
        }

//开始恢复
private void btnRestore_Click(object sender, EventArgs e)
        {
            try
            {
                if (restorePath == "")
                {
                    MessageBox.Show("请先选择数据库恢复路劲", "提示");
                }
                //以下代码用于关闭正在使用数据库的进程
                SqlConnection.ClearAllPools();
                string conStr = "Data Source = .;Database = master;Integrated security = True";
                CDataBase.conn.ConnectionString = conStr;
                CDataBase.conn.Open();
                string sqlStr = "select spid from master..sysprocesses where dbid=db_id('数据库名')";
                SqlDataAdapter sda = new SqlDataAdapter(sqlStr, CDataBase.conn);
                DataTable spidTable = new DataTable();
                sda.Fill(spidTable);
                SqlCommand cmd1 = new SqlCommand();
                cmd1.CommandType = CommandType.Text;
                cmd1.Connection = CDataBase.conn;
                for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
                {
                    //强行关闭用户进程
                    cmd1.CommandText = "kill" + spidTable.Rows[iRow][0].ToString();
                    cmd1.ExecuteNonQuery();
                }
                CDataBase.conn.Close();
                CDataBase.conn.Dispose();
                string restoreStr = "use master restore database 数据库名from disk='"+restorePath +"'" +" with replace";
                CDataBase.conn.ConnectionString = CDataBase.connstr;
                CDataBase.conn.Open();
                SqlCommand cmd2 = new SqlCommand(restoreStr, CDataBase.conn);
                cmd2.ExecuteNonQuery();
                CDataBase.conn.Close();
                if (MessageBox.Show("数据库恢复成功", "提示", MessageBoxButtons.OK) == DialogResult.OK)
                {
                    this.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                CDataBase.conn.Close();
            }
        }