Executing sqlbackup from .NET Code

cjbreisch

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?

Eddie D

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

cjbreisch

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 Donahue

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