Executing sqlbackup from .NET Code

cjbreischcjbreisch Posts: 3
edited March 6, 2010 5:33PM in SQL Backup Previous Versions
Hello,
I'm getting exitcode 870 when attempting to use the extended stored procedures from .NET.

Here's my code:
public void DoRestore(string commandString)
{
SqlConnection conn = new SqlConnection(...);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "master..sqlbackup";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@commandString", commandString);

SqlDataReader reader = cmd.ExecuteReader();
...
}

I go to the second result set and exitcode = 870, sqlerrorcode = -1

commandString has the value:
"'-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\"'"

All paths and database names are valid. I've tried the file names both with brackets ([file]) and double single quotes (''file''). I've also tried setting the initial db to master and setting CommandText to "sqlbackup" rather than "master..sqlbackup". Same results no matter what I do.

Help?

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    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
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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();
            }
        }
    }
    
Sign In or Register to comment.