SqlCommand back up Database

Question

I have an c# winforms application (.net 2 framework). I need to backup data bases from my application. I am trying to do this by executing an SqlCommand asynchronously. The code is executed with no exceptions but I dont get the .bak file in my destination...

this is the code :

#region backup DB using T-SQL command

string connString = "Data Source=" + ConfigurationManager.AppSettings.Get("localhost_SQLEXPRESS") + ";Initial Catalog=" + db + ";UserID=" + ConfigurationManager.AppSettings.Get("user") + ";Password=" + ConfigurationManager.AppSettings.Get("password");
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connString);
builder.AsynchronousProcessing = true;
using (SqlConnection sqlConnection1 = new SqlConnection(builder.ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("BACKUP DATABASE " + db + " TO DISK=" + location + "\\" + ConfigurationManager.AppSettings.Get("DataBaseBackupsFolderName") + "\\" + db + ".bak'", sqlConnection1))
    {    
        sqlConnection1.Open();    
        IAsyncResult result = cmd.BeginExecuteNonQuery();

        while (!result.IsCompleted)
        {
            Thread.Sleep(100);
        }
    }
}
#endregion

Could you put a breakpoint on the sqlConnection1.Open() and check the value of cmd.CommandText (in particular the filename path) — Steve
– Steve, Commented Jul 4, 2012 at 10:10
Just to make sure... The backup is stored on the server, not on the computer that issued the command (except if both are the same machine). — coffee_machine
– coffee_machine, Commented Jul 4, 2012 at 10:16
@Steve This is the command - BACKUP DATABASE DB_Tags_TestingTagBurner TO DISK=D:\New folder\DataBaseBackups\DB_Tags_TestingTagBurner.bak' — N.D
– N.D, Commented Jul 4, 2012 at 10:50

BugFinder · Accepted Answer · 2012-07-04 10:14:04Z

3

In your SQL backup line you seem to be missing a single quote at the beginning of the path to the backup file.

  using (SqlCommand cmd = new SqlCommand("BACKUP DATABASE " + db + " TO DISK='" + location + "\\" + ConfigurationManager.AppSettings.Get("DataBaseBackupsFolderName") + "\\" +db + ".bak'", sqlConnection1))

answered Jul 4, 2012 at 10:14

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego · Accepted Answer · 2012-07-04 10:15:53Z

1

Two advices to try to isolate the problem:

1) Get the resulting string (the one you are executing on the SqlCommand and run it manually on SQL Server to make sure the backup commnad is correct.

2) Try a synchronous command with a regular ExecuteNonQuery to see if you are getting a SQL Server exception

answered Jul 4, 2012 at 10:15

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Comments

Efran Cobisi · Accepted Answer · 2012-07-04 10:17:12Z

1

You should call EndExecuteNonQuery() on your SqlCommand instance in order to throw any eventual exception and thus understand what is wrong with your SQL statements:

IAsyncResult result = cmd.BeginExecuteNonQuery();

// Wait for the command to complete

result.AsyncWaitHandle.WaitOne();

// End the execution and throw any eventual exception

cmd.EndExecuteNonQuery(result);

As you can see, I have also replaced your original Thread.Sleep() cycle block with a more effective wait on the wait handle of the command.

Quoting MSDN:

For each call to BeginOperationName, the application should also call EndOperationName to get the results of the operation.

edited Jul 4, 2012 at 10:17

answered Jul 4, 2012 at 10:11

Efran Cobisi

6,5141 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

SqlCommand back up Database

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related