使用C#备份数据库

---

备份没问题

// 备份方法
// 要修改Database，User ID，Password（SQL验证模式）
// 数据库名，验证id，验证密码（需要数据库设置为SQL验证）
SqlConnection conn = new SqlConnection("Server=.;Database=water;User ID=sa;Password=sa;");

SqlCommand cmdBK = new SqlCommand();
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
// 文件位置
string rootPath = Server.MapPath("~/");
string filePath = rootPath + "water.bak";
// 要修改database名字
cmdBK.CommandText = "backup database water to disk='" + filePath + "' with init";

try
{
    conn.Open();
    cmdBK.ExecuteNonQuery();
    Response.Write("<script>alert('备份成功!')</script>");
}
catch (Exception)
{
    Response.Write("<script>alert('备份失败!')</script>");
}
finally
{
    conn.Close();
    conn.Dispose();
}

恢复出bug

// 还原方法
// 要修改Database，User ID，Password（SQL验证模式）
SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");
conn.Open();

//KILL DataBase Process
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
ArrayList list = new ArrayList();
while(dr.Read())
{
	list.Add(dr.GetInt16(0));
}
dr.Close();
for(int i = 0; i < list.Count; i++)
{
	cmd = new SqlCommand(string.Format("KILL {0}", list[i]), conn);
	cmd.ExecuteNonQuery();
}

SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text;
cmdRT.Connection = conn;
// 文件位置
string rootPath = Server.MapPath("~/");
string filePath = rootPath + "water.bak";
// 要修改database名字
cmdBK.CommandText = "restore database water from disk='" + filePath + "'";

try
{
	cmdRT.ExecuteNonQuery();
	MessageBox.Show("Restore successed.");
}
catch(Exception ex)
{
	MessageBox.Show(ex.Message);
}
finally
{
	conn.Close();
}

---

Reference

使用C#备份数据库