ASP. NET backup and restoration of SQL Server databases
1. Backup
SqlConnection connection = new SqlConnection (ConfigurationManager. etettings ["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 = "Database Backup succeeded ";
}
Catch (Exception ex)
{
LblBackup. Text = ex. Message;
}
Finally
{
Connection. Close ();
}
Bind (); // a new backup file is generated and the update drop-down box is displayed.
2. Restore
SqlConnection conn = new SqlConnection ("Server =. \ sqlexpress; database = master; uid = sa; pwd = sa ");
String dbFileName = ddlRestore. SelectedValue;
If (dbFileName = string. Empty)
{
LblRestore. Text = "no database backup file, please back up first ";
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 = "database restored successfully ";
Session. Abandon ();
Response. Redirect ("login. aspx ");
}
Catch (System. Exception ex)
{
LblRestore. Text = ex. Message;
}
Finally
{
Conn. Close ();
}
1. Backup
SqlConnection connection = new SqlConnection (ConfigurationManager. etettings ["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 = "Database Backup succeeded ";
}
Catch (Exception ex)
{
LblBackup. Text = ex. Message;
}
Finally
{
Connection. Close ();
}
Bind (); // a new backup file is generated and the update drop-down box is displayed.
2. Restore
SqlConnection conn = new SqlConnection ("Server =. \ sqlexpress; database = master; uid = sa; pwd = sa ");
String dbFileName = ddlRestore. SelectedValue;
If (dbFileName = string. Empty)
{
LblRestore. Text = "no database backup file, please back up first ";
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 = "database restored successfully ";
Session. Abandon ();
Response. Redirect ("login. aspx ");
}
Catch (System. Exception ex)
{
LblRestore. Text = ex. Message;
}
Finally
{
Conn. Close ();
}