How to Backup and Restore SQL Express 2005 (AttachDbFilename mode)
At my last project, I was forced to write two functions for Backup and Restore SQL Express 2005 in a windows application. Application connect to database with AttachDbFilename mode. ( I did not use "Initial Catalog" in connection string for some reasons)
When using AttachDbFilename in connection string ,SQL Server attach database in runtime.
SQL Server attach database with fully-qualified name (in example "D:\Program\DB\MyDatabase.mdf").
Backup and Restore SQL Express 2005 is tricky.
I wrote two function for Backup and Restore that works like a charm for windows and web applications.
Here are two functions:
public string DBFileName
{
get
{
return Request.PhysicalApplicationPath + "App_Data\\Database.mdf";
}
}
public string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
}
}
public void Backup()
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand("backup database [" + DBFileName + "] to disk=@path with format", con);
cmd.Parameters.AddWithValue("@path",Request.PhysicalApplicationPath + "\\App_Data\\Database.bak");
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
public void Restore()
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
string query = "USE [master]; RESTORE DATABASE [" + DBFileName + "] FROM DISK = N'" +
Request.PhysicalApplicationPath + "\\App_Data\\Database.bak" + " ' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
Note the place of brackets "[" "]" and other parts more carefully.
For windows applications, story is the same but you have to use proper database file address.