ASP.NET中使用代码来进行备份和还原数据库

时间:2022-03-31 13:15:49

 SQL代码:

 

-- 备份数据库
backup database db_CSManage to disk='c:\backup.bak'
-- 还原数据库,必须先备份该数据库的日志文件到原先的备份文件中
backup log db_CSManage to disk='c:\backup.bak'
restore database db_CSManage from disk='c:\backup.bak'

 

 

其中db_CSManage是数据库名称,disk后的路径即是备份文件存储的路径。
知道了SQL语句,那么在.NET代码中就容易多了,备份的.NET代码如下:

C#代码-备份: 

 

try
{
	if (txtPath.Text != "" && txtName122.Text != "")
	{
		getSqlConnection geCon = new getSqlConnection();
		SqlConnection con = geCon.GetCon();

		string strBacl = "backup database db_CSManage to disk='" + txtPath.Text.Trim() + "\\" + txtName.Text.Trim() + ".bak'";
		SqlCommand Cmd = new SqlCommand(strBacl, con);
		if (Cmd.ExecuteNonQuery() != 0)
		{
			MessageBox.Show("数据备份成功!", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
			this.Close();
		}
		else
		{
			MessageBox.Show("数据备份失败!", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Information);
		}

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

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

 

 

 

以下是还原数据库的代码,在示例中发现开头得先删除与该数据库相关的进程,然后在还原之前得先把数据库日志备份到开始的备份文件中,然后才还原备份文件,要不然会出错的,代码如下:  

C#代码-还原: 

 

if (textPaht.Text != "")
{
	getSqlConnection geCon = new getSqlConnection();
	SqlConnection con = geCon.GetCon();
	if (con.State == ConnectionState.Open)
	{
		con.Close();
	}
	string DateStr = "Data Source=niunan\\sqlexpress;Database=master;User id=sa;PWD=123456";
	SqlConnection conn = new SqlConnection(DateStr);
	conn.Open();

	//-------------------杀掉所有连接 db_CSManage 数据库的进程--------------
	string strSQL = "select spid from master..sysprocesses where dbid=db_id( 'db_CSManage') ";
	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();
	string sql = "backup log db_CSManage to disk='" + textPaht.Text.Trim() + "' restore database db_CSManage from disk='" + textPaht.Text.Trim() + "'";
	SqlCommand sqlCmd = new SqlCommand(sql, 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);
}

 

 

转自:http://niunan.javaeye.com/blog/321062