数据库备份与还原

[c#]代码库

  private void button1_Click(object sender, EventArgs e)
        {
           
            string day = DateTime.Today.ToShortDateString();
            string time = DateTime.Now.ToShortTimeString();
            //string time1 = time.Substring(0, time.IndexOf(":"))+time.Substring((time.IndexOf(":")+1),time.Length-1);
            string time1 = time.Substring(0, time.IndexOf(":")).ToString() + time.Substring((time.IndexOf(":") + 1)).ToString();
            string date = day + time1;
            string date1 = day + date;
            int flag = 0;
            string SqlStr1 = "server=TXQ\\TXQ;uid=sa;pwd=123;database=aa";
            string SqlStr2 = "backup database aa to disk='C:\\DBBACK\\" + date+ ".bak'";
            SqlConnection con = new SqlConnection(SqlStr1);
            con.Open();

            try
            {

               SqlCommand com = new SqlCommand(SqlStr2, con);
                  com.ExecuteNonQuery();

                  string SqlStr3 = "server=TXQ\\TXQ;uid=sa;pwd=123;database=backUp";
                  string SqlStr4 = "insert into back (name,path) values('" + date1 + "','" + "C:\\DBBACK\\" + date + ".bak')";
                   SqlConnection cn = new SqlConnection(SqlStr3);
                   cn.Open();
                   SqlCommand cm = new SqlCommand(SqlStr4,cn);
                  flag=cm.ExecuteNonQuery();
                  if (flag > 0)
                  {
                      MessageBox.Show("数据库备份成功！");
                  }
                  cn.Close();
               

            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message);
                MessageBox.Show("数据库备份失败！");

            }
            finally
            {
                
                con.Close();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“backUpDataSet.back”中。您可以根据需要移动或移除它。
            //this.backTableAdapter.Fill(this.backUpDataSet.back);

        }

        private void button2_Click(object sender, EventArgs e)
        {
            int check = 0;
            int sel = 0;
            for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
            {
                if (dataGridView2.Rows[i].Cells[1].Value != null)
                {
                    if (Convert.ToBoolean(this.dataGridView2.Rows[i].Cells[0].Value))
                    {
                        check++;
                        sel = i;
                    }
                }
            }
            if (check > 1)
            {
                MessageBox.Show("您只能选择一文件还原！","提示" ,MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            if (check ==0)
            {
                MessageBox.Show("请选择还原的文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                string DateStr = "server=TXQ\\TXQ;uid=sa;pwd=123;database=aa";
                string path = this.dataGridView2.Rows[sel].Cells[4].Value.ToString();
                SqlConnection conn = new SqlConnection(DateStr);
                conn.Open();
               
                string strSQL = "select spid from master..sysprocesses where dbid=db_id( 'aa') ";
                SqlDataAdapter Da = new SqlDataAdapter(strSQL, conn);

                DataTable spidTable = new DataTable();
                Da.Fill(spidTable);
                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandType = CommandType.Text;
                Cmd.Connection = conn;
                if (spidTable.Rows.Count > 1)
                {
                    for (int iRow = 0; iRow < spidTable.Rows.Count - 1; iRow++)
                    {
                        Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();   
                        Cmd.ExecuteNonQuery();
                    }
                }
                conn.Close();
                conn.Dispose();
                          
                try
                {
                    string str = "use master restore database aa from Disk='" + path + "'  with replace,stats=10";
                    SqlConnection conn1 = new SqlConnection(DateStr);
                    conn1.Open();
                    SqlCommand cmd = new SqlCommand(str, conn1);
                    cmd.ExecuteNonQuery();
                    if (MessageBox.Show("恢复成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) == DialogResult.OK)
                    {
                        this.Close();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }


            }

        private void button3_Click(object sender, EventArgs e)
        {
            int check = 0;
            int[] sel = new int[this.dataGridView2.Rows.Count];
            int flag;
        
           
                for (int i = 1; i < this.dataGridView2.Rows.Count; i++)
                {
                    if (dataGridView2.Rows[i].Cells[1].Value != null)
                    {
                        // Object o = this.dataGridView2.Rows[i].Cells[0].Value;
                        if (Convert.ToBoolean(this.dataGridView2.Rows[i].Cells["sel"].Value))
                        {
                            check++;

                            sel[i] = i;
                        }
                    }
                }
            
            if (check == 0)
            {
                MessageBox.Show("请选择要删除的文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                
                    SqlConnection cn1 = new SqlConnection();
                    SqlCommand cm1 = new SqlCommand();
                    cn1.ConnectionString = "server=TXQ\\TXQ;uid=sa;pwd=123;database=backUp";
                    cm1.Connection = cn1;
                    cm1.CommandType = CommandType.Text;
                    cn1.Open();
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataTable dt = new DataTable();
                try
               {
                    for (int i = 0; i < this.dataGridView2.Rows.Count; i++)
                    {
                        if (Convert.ToBoolean(this.dataGridView2.Rows[0].Cells["sel"].Value))
                        {
                            cm1.CommandText = "delete from back where id=0";
                            cm1.ExecuteNonQuery();
                            i++;

                        }
                       
                        flag = sel[i];
                        if (flag != 0)
                        {
                            int id = Convert.ToInt32(this.dataGridView2.Rows[flag].Cells[1].Value);
                            cm1.CommandText = "delete from back where id=" + id;
                            cm1.ExecuteNonQuery();
                        }
                       
                      
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
                finally
                {
                    cn1.Close();
                    BindingSource bing = new BindingSource();
                    cm1.CommandText = "select id as '编号',name as '名称',time as '时间',path as '路径' from back order by id desc";
                    da.SelectCommand = cm1;
                    da.Fill(dt);
                    bing.DataSource= dt;
                    this.dataGridView2.DataSource = bing;
                   
                }
            }
        }

        private void tabControl1_TabIndexChanged(object sender, EventArgs e)
        {
            
        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cm = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            conn.ConnectionString = "server=TXQ\\TXQ;uid=sa;pwd=123;database=backUp";
            cm.Connection = conn;
            cm.CommandText = "select * from back order by id desc";
            da.SelectCommand = cm;
            da.Fill(dt);
          
            this.dataGridView2.DataSource = dt;
           
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }