none
Backup LocalDB using C# RRS feed

  • Question

  • Hi,

        I have written an application and in the application there is an option to backup a localDb database to the users "MyDocuments" directory.

    Unfortunately I am having issues with the backup as it keeps failing during execution, here is some of my code;

    Backup Code:

            private void CreateBackup(string database)
            {
                try
                {
                    string appPath = Application.StartupPath;
    
                    // Set backup folder
                    var backupFolder = string.Format("{0}\\", Environment.GetFolderPath(Environment.SpecialFolder.Windows));
                    var moveFileFolder = string.Format("{0}\\", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments));
                    backupFolder += string.Format("{0}", "Temp\\");
                    var sqlConStrBuilder = new SqlConnectionStringBuilder(ConnString);
    
                    // set backupfilename 
                    var backupFileName = String.Format("{0}{1}_{2}.bak", backupFolder, "WorkTracker", DateTime.Now.ToString("yyyyMMdd"));
                    var moveFileName = String.Format("{0}{1}_{2}.bak", moveFileFolder, "WorkTracker", DateTime.Now.ToString("yyyyMMdd"));
    
                    using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString))
                    {
                        var dbLoc = string.Format("{0}\\AppData\\WorkTracker.mdf", appPath);
                        var query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", dbLoc, backupFileName);
                        using (var command = new SqlCommand(query, connection))
                        {
                            connection.Open();
                            command.ExecuteNonQuery();
                        }
    
                        if (File.Exists(moveFileName))
                        {
                            File.Delete(moveFileName);
    
                            // Move the file.
                            File.Move(backupFileName, moveFileName);
                            meBackupResults.Text += string.Format("Backup Completed: {0}", moveFileName);
                        }
                        else
                        {
                            // Move the file.
                            File.Move(backupFileName, moveFileName);
                            meBackupResults.Text += string.Format("Backup Completed: {0}", moveFileName);
                        }
                    }
                }
                catch (Exception ex)
                {
                    meBackupResults.Text += string.Format("Backup failure: {0}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace);
                }
    
            }
        }

    Connection String: (Using dbBackup)

      <connectionStrings>
        <add name="WorkTracker" connectionString="XpoProvider=MSSqlServer;Data Source=(LocalDB)\V11.0;AttachDbFilename=\AppData\WorkTracker.mdf;Integrated Security=True"/>
        <add name="dbBackup" connectionString="Data Source=(LocalDB)\V11.0;AttachDbFilename=\AppData\WorkTracker.mdf;Integrated Security=True" />
      </connectionStrings>

    The error message I get is:

    Backup failure: An attempt to attach an auto-named database for file \AppData\WorkTracker.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    Any help would be greatly appreciated.

    Regards..

    Peter.

    Monday, July 13, 2015 12:57 PM

Answers

  • Hi Peter,

    I have reproduced your code, you forget to add  double quotes. After adding, it works fine.

     using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString))
                    {
                        var dbLoc = string.Format("{0}\\AppData\\WorkTracker.mdf", appPath);
                        var query = String.Format("BACKUP DATABASE \"{0}\" TO DISK='{1}'", dbLoc, backupFileName);
                        using (var command = new SqlCommand(query, connection))
                        {
                            connection.Open();
                            command.ExecuteNonQuery();
                        }
    

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Buda_56 Wednesday, July 22, 2015 10:05 AM
    Wednesday, July 22, 2015 6:40 AM

All replies

  • Have you tried to specify the database name instead of the path? Probably BACKUP DATABASE WorkTracker TO….

    Monday, July 13, 2015 5:03 PM
  • Hi,

       Thanks for the response, yes I tried that as well. I found a google post that advised to change it to the full path with the filename.

    Regards..

    Peter

    Monday, July 13, 2015 9:46 PM
  • Hi Peter,

    I had this problem before. Changing the attacheddbfilename property to the direct path of the mdf file will fix this issue.

    Attachdbfilename=C:\Users\AppData\WorkTracker.mdf;User Instance=True;integrated 

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 14, 2015 5:30 AM
  • Kristin,

        Thanks for your reply, unfortunately that didn't work either. When I debug through I can see that it is failing on the connection.

    I changed the connection string to the following and it now opens a connection okay but fails when executing the command.ExecuteNonQuery().

    It now gives me the following error:

    Backup failure: Incorrect syntax near 'C:'.

    dbBackup Connection String is now:

      <connectionStrings>
        <add name="WorkTracker" connectionString="XpoProvider=MSSqlServer;Data Source=(LocalDB)\V11.0;AttachDbFilename=\AppData\WorkTracker.mdf;Integrated Security=True"/>
        <add name="dbBackup" connectionString="Data Source=(LocalDB)\V11.0;AttachDbFilename=|DataDirectory|\AppData\WorkTracker.mdf;Integrated Security=True" />
      </connectionStrings>Regards..

    Regards..

    Peter

    Tuesday, July 14, 2015 10:53 AM
  • Hi Peter,

    Per my understanding, using full path is no doubt.

    >>Backup failure: Incorrect syntax near 'C:'.

    Based on the error message seems a SQL syntax issue more than a C# code problem.

    1. How about you execute the exactly same command you build with C# in Microsoft SqlServer management studio?
    2. Make sure you build the exactly command which will work fine with SSMS.

     

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Kristin Xie Thursday, July 16, 2015 8:34 AM
    Thursday, July 16, 2015 8:31 AM
  • Kristin,

          Thanks fro your reply, sorry I took so long to reply. That doesn't work either, here's the syntax that I user..

    BACKUPDATABASE"C:\Users\buda5\Documents\Visual Studio 2013\Projects\WorkTracker\WorkTracker\bin\Debug\AppData\WorkTracker.mdf"TODISK='C:\Windows\Temp\WorkTracker_150719.bak'

    Error Message:

    Msg 911, Level 16, State 11, Line 1

    Database 'C:\Users\buda5\Documents\Visual Studio 2013\Projects\WorkTracker\WorkTracker\bin\Debug\AppData\WorkTracker.mdf' does not exist. Make sure that the name is entered correctly.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    The database is only attached at runtime and therefore I don't see how I could back it up directly from SQL Mgt Studio.

    Regards..

    Peter.

    Sunday, July 19, 2015 11:04 AM
  • Hi Peter,

    I have reproduced your code, you forget to add  double quotes. After adding, it works fine.

     using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString))
                    {
                        var dbLoc = string.Format("{0}\\AppData\\WorkTracker.mdf", appPath);
                        var query = String.Format("BACKUP DATABASE \"{0}\" TO DISK='{1}'", dbLoc, backupFileName);
                        using (var command = new SqlCommand(query, connection))
                        {
                            connection.Open();
                            command.ExecuteNonQuery();
                        }
    

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Buda_56 Wednesday, July 22, 2015 10:05 AM
    Wednesday, July 22, 2015 6:40 AM
  • Kristin,

            Thanks for your persistence, that was the solution and it is greatly appreciated. Your a legend.

    Regards..

    Peter.

    Wednesday, July 22, 2015 10:06 AM