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.

Published Thursday, October 8, 2009 3:43 PM by mlife

Comments

No Comments

Leave a Comment

(required) 
(required) 
(optional)
(required)