How to create database backup, when DB not stored in Microsoft SQL Server?

Question

I am building a C# application with SQL Server 2008 R2 database which is stored in the bin folder of my project. I use Linq to Sql method to create the database and attach it to my project.

The problem that I'm having is that when I'm trying to create a backup of my database. It throws an error saying

Database (database_name) does not exist make sure the name is entered correctly. BACKUP DATABASE is terminating abnormally.

Here is the code that i write on my button click event:

try
{
    SaveFileDialog sd = new SaveFileDialog();
    sd.Filter = "SQL Server database backup files|*.bak";
    sd.Title = "Create Database Backup";

    if (sd.ShowDialog() == DialogResult.OK)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string sqlStmt=string.Format("BACKUP DATABASE <database_name> TO DISK='{0}'",sd.FileName);

            using (SqlCommand bu2 = new SqlCommand(sqlStmt, conn))
            {
                conn.Open();
                bu2.ExecuteNonQuery();
                conn.Close();

                MessageBox.Show("Backup Created Sucessfully");
            }
        }
    }
}
catch(Exception ex)
{
    MessageBox.Show(ex.ToString());
}

My Connection String:

string connStr = ConfigurationManager.ConnectionStrings["project_name.Properties.Settings.project‌​_nameConnectionString"].ConnectionString; 

and then from my app.config file

<add name="project_name.Properties.Settings.project_nameConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database_name.mdf;Integrate‌​d Security=True;User Instance=True" providerName="System.Data.SqlClient" /> 

so if any one have a solution to my problem that will be helpful.

Answer 1

After a really long time brain storming, I finally succeded so, here is the solution:

try
{
    SaveFileDialog sd = new SaveFileDialog();
    sd.Filter = "SQL Server database backup files|*.bak";
    sd.Title = "Create Database Backup";

    if (sd.ShowDialog() == DialogResult.OK)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["project_name.Properties.Settings.project‌​_nameConnectionString"].ConnectionString))
        {
            string sqlStmt = string.Format("backup database [" + System.Windows.Forms.Application.StartupPath + "\\dbname.mdf] to disk='{0}'",sd.FileName);
            using (SqlCommand bu2 = new SqlCommand(sqlStmt, conn))
            {
                conn.Open();
                bu2.ExecuteNonQuery();
                conn.Close();

                MessageBox.Show("Backup Created Sucessfully");
            }                   
        }
    }
}
catch (Exception)
{
    MessageBox.Show("Backup Not Created");
}

Resource from this question: How to backup from .mdf database that I created

Answer 2

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

Also, since the .mdf file is not permanently attached to a SQL Server instance, you cannot use commands like BACKUP DATABASE on it.

In my opinion, the real solution would be to

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. MyDatabase)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=MyDatabase;Integrated Security=True
   

   and everything else is exactly the same as before...

And then, once you've done this, you can also run your backups using BACKUP DATABASE MyDatabase .... without any problems