Executing sqlbackup from .NET Code

Comments

• Eddie D Redgate › Posts: 1,803 Rose Gold 5

  March 5, 2010 12:48PM

  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 Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• cjbreisch Posts: 3

  March 5, 2010 1:09PM

  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.

  0 · Share on Twitter
• Brian Donahue Posts: 6,590 Bronze 1

  March 6, 2010 5:33PM

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

  0 · Share on Twitter