Executing sqlbackup from .NET Code

• 
  Thank you for your post into the forum.
  
  SQL Backup exit code 870 indicates that no command was passed to SQL Backup. The command is empty.
  
  So I suspect that you .Net application is not passing the commands correctly.
  
  The command using your example needs to be as follows:

  EXCUTE master..sqlbackup '-SQL "RESTORE DATABASE [testDB] FROM DISK = [\\testServer\SQLbackups\testDB.sqb] WITH STANDBY = [D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\\Undo_testDB.dat], DISCONNECT_EXISTING, MOVE [testeDB] TO [E:\SQLdata\testDB.mdf], MOVE [testDB_log] TO [E:\SQLLogs\testeDB_log.ldf], MOVE [testDB_2_Data] TO [E:\SQLdata\testeDB2_data.ndf],  REPLACE, ORPHAN_CHECK" '
  

  
  If you run the above code from a new query window and it is successful, the problem must be an error in your .Net code. I am not a programmer, so I cannot help with regards to your .Net code.
  
  I hope this helps to answer your question.
  
  Many Thanks
  Eddie

  Eddie D March 05, 2010 12:48

  - 0 +
• 
  Yes, the command works fine from inside SQL Server Management Studio.
  
  I believe that my .NET code is correct, unless your stored proc is processing the parameters in some odd way. That exact code works for any other stored procs that I call.
  
  So, I'm punting it back to you.

  cjbreisch March 05, 2010 13:09

  - 0 +
• 
  Hi,
  
  You don't need the leading single-quote in your command text. That's what's gumming up the works. Here is a code sample, including how to get at the SQL error codes and SQL Backup error codes that you can use for error handling.

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Data.SqlClient;
  using System.Data;
  
  namespace execSqlBackup
  {
      class Program
      {
          static string info = String.Empty;
          static void Main(string[] args)
          {
              SqlConnection conn = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=MASTER;Integrated Security=SSPI");
              
              conn.Open();
              SqlCommand cmd = conn.CreateCommand();
              cmd.CommandText = "master..sqlbackup"; 
              cmd.CommandType = CommandType.StoredProcedure; 
              string commandString="-SQL \"BACKUP DATABASE [MASTER] to DISK=[<AUTO>.sqb]\"";
              cmd.Parameters.Add(new SqlParameter("@commandString", commandString));
              cmd.Parameters.Add(new SqlParameter("@exitcode",SqlDbType.Int));
              cmd.Parameters.Add(new SqlParameter("@backuperrorcode", SqlDbType.Int));
              cmd.Parameters.Add(new SqlParameter("@sqlerrorcode", SqlDbType.Int));
              cmd.Parameters["@exitcode"].Direction = ParameterDirection.ReturnValue;
              cmd.Parameters["@sqlerrorcode"].Direction = ParameterDirection.Output;
              cmd.Parameters["@backuperrorcode"].Direction = ParameterDirection.Output;
              SqlDataReader reader = cmd.ExecuteReader();
              while (reader.Read())
              {
                  //Print the results to stdout
                  Console.WriteLine(reader.GetString(0));
              }
              reader.Close();
              // SQL Backup will return the error codes and save you the trouble of parsing the output
              Console.WriteLine("SQL Backup Error: " + cmd.Parameters["@backuperrorcode"].Value);
              Console.WriteLine("SQL Error: " + cmd.Parameters["@sqlerrorcode"].Value);
              // @exitcode is either 1 or 0 where 1 is a failure
              Console.WriteLine("SQL Backup exit code: " + cmd.Parameters["@exitcode"].Value);
              conn.Close();
          Console.ReadLine();
          }
      }
  }
  

  Brian Donahue March 06, 2010 17:33

  - 0 +

Add comment

Please sign in to leave a comment.