ASP.NET备份与还原SQL Server数据库
1.备份
SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["Connection"]);
string dbFileName = DateTime.Now.ToString("yyyyMMddHHmmss")+".bak";
try
{
SqlCommand command = new SqlCommand("use master;backup database xxdl to disk=@path;", connection);
connection.Open();
string path = Server.MapPath("~\\App_Data") + "\\" + dbFileName;
command.Parameters.AddWithValue("@path", path);
command.ExecuteNonQuery();
lblBackup.Text = "数据库备份成功";
}
catch (Exception ex)
{
lblBackup.Text = ex.Message;
}
finally
{
connection.Close();
}
Bind();//产生了新备份文件,更新下拉框
2 还原
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Connection"]);
string dbFileName = ddlRestore.SelectedValue;
if (dbFileName == string.Empty)
{
lblRestore.Text = "没有数据库备份文件,请先备份";
return;
}
try
{
string sql2 = "Alter Database xxdl Set Offline with Rollback immediate;use master;restore database xxdl from disk=@path With Replace;Alter Database xxdl Set OnLine With rollback Immediate;";
SqlCommand command = new SqlCommand(sql2,conn);
conn.Open();
string path = Server.MapPath("~\\App_Data") + "\\" + dbFileName;
command.Parameters.AddWithValue("@path", path);
command.ExecuteNonQuery();
lblRestore.Text = "数据库还原成功";
}
catch (System.Exception ex)
{
lblRestore.Text = ex.Message;
}
finally
{
conn.Close();
Session.Abandon();
Response.Redirect("login.aspx");
}