C#备份与恢复sql数据库的简单代码

最新推荐文章于 2021-02-11 11:03:17 发布

fhb19870610

最新推荐文章于 2021-02-11 11:03:17 发布

阅读量914

点赞数

文章标签：数据库 c# sql database command string

本文链接：https://blog.csdn.net/fhb19870610/article/details/4442184

版权

采用C#代码备份数据库到指定路径的方法：

string ConnectionString = "SERVER=(Local);database=SellManage;Integrated Security =True";
SqlConnection myCon = new SqlConnection(ConnectionString);

myCon.Open();
string SqlIns = "backup database SellManage to disk='" + Path+ "//" + BackupName+ ".dat'";
SqlCommand command = new SqlCommand();
command.CommandText = SqlIns;
command.Connection = myCon;
command.ExecuteNonQuery();

恢复数据库到系统的方法如下（必须杀死所有跟该数据库有关的进程）

try
                {
                    string ConnectionString = "SERVER=(Local);database=master;Integrated Security =True";
                SqlConnection myCon = new SqlConnection(ConnectionString);
                myCon.Open();
                #region 杀掉所有连接SellManage数据库的进程
                string strSQL = "select spid from master..sysprocesses where dbid=db_id( 'daname') ";
                SqlDataAdapter Da = new SqlDataAdapter(strSQL, myCon);
                DataTable spidTable = new DataTable();
                Da.Fill(spidTable);
                SqlCommand Cmd = new SqlCommand();
                Cmd.CommandType = CommandType.Text;
                Cmd.Connection = myCon;
                for (int iRow = 0; iRow < spidTable.Rows.Count; iRow++)
                {
                    Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();   //强行关闭用户进程
                    Cmd.ExecuteNonQuery();
                }
                myCon.Close();
                myCon.Dispose();
                #endregion
                SqlConnection myCon1 = new SqlConnection(ConnectionString);
                myCon1.Open();
                string SqlIns = "restore database SellManage from disk='" + RestorePath+ "' ";
                SqlCommand command1 = new SqlCommand(SqlIns, myCon1);
                command1.ExecuteNonQuery();
                command1.Dispose();
                myCon1.Close();
                myCon1.Dispose();
                  MessageBox.Show("数据还原成功！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                catch
                    {
                        MessageBox.Show("数据还原失败！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }

但是如果要定时或者安全的备份跟还原数据库最好是在存储过程中实现