how to back up my MDF file

Question

3.67/5 (2 votes)

See more:

Hello All

I have generated an windows application. I have used .MDF file.
The project is working fine but I want to backup my .Mdf file on a button click. When i copy this file from bin folder tuser location then it gives an error
like 'The process cannot access the file '.mdf' because it is being used by another process'.

What code should i use/ technique should i use so I can do so.

I am using :

Visual Studio 2010,
dot net framwork 3.5 sp1
MDF FILE of Microsoft Sql server 2005.

Here is the coding

C#

con="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True;User Instance=True"

 SaveFileDialog SaveFD1 = new SaveFileDialog();
 string FileName = "";
 SaveFD1.FileName = "Test";
 SaveFD1.Title = "Backup ";
 SaveFD1.Filter = "Database files (*.mdf)|*.mdf";
 SaveFD1.FilterIndex = 1;
 SaveFD1.DefaultExt = "mdf";
 SaveFD1.RestoreDirectory = true;
 if (SaveFD1.ShowDialog() == DialogResult.OK)
  {                    
    SqlConnection.ClearAllPools();
    Ut.con.Close();
    FileName = SaveFD1.FileName
    string sourcePath = Application.StartupPath;
    string src = sourcePath + "\\Test.mdf";
    string dst = path;                   
    System.IO.File.Copy(src, dst, true);
 }

Posted 18-Jan-15 18:59pm

ErBhati

Updated 18-Jan-15 19:16pm

v4

Add a Solution

2 solutions

Solution 1

You don't take a backup by copying an active MDF file (or any other database file). Because of the buffering mechanisms the data may be in inconsistent state.

Instead, use the BACKUP[^] statement to get a consistent backup somewhere and then copy that file to a safe location.

Posted 18-Jan-15 19:23pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ErBhati · Accepted Answer · 2015-01-18T19:43:00

Here is the solution. I found it from many days googled..

C#

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");
}