0

I wrote a SQL Server stored procedure to backup a database:

ALTER PROCEDURE [dbo].[usp_backup_database]
    (@DBName sysname = NULL)
AS
BEGIN
    SET NOCOUNT ON;

    IF LEN (ISNULL (@DBName, '')) > 0
    BEGIN
        DECLARE @path VARCHAR(256) = 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\';
        DECLARE @fileName VARCHAR(256);
        DECLARE @fileDate VARCHAR(50) = REPLACE (CONVERT (VARCHAR(50), GETDATE (), 126), ':', '-');

        SET @fileName = @path + @DBName + '_' + @fileDate + '.BAK';

        BEGIN TRY
            BACKUP DATABASE @DBName 
            TO DISK = @fileName WITH COMPRESSION;
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;

            SELECT 
                @ErrorMessage  = @fileName + N' ' + ERROR_MESSAGE (),
                @ErrorSeverity = ERROR_SEVERITY (),
                @ErrorState    = ERROR_STATE ();

            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
        END CATCH
    END
END

Calling it from within SSMS works fine, that is exec usp_backup_database 'dbname' successfully backs up dbname every time!

However, I have a C# program that I call this stored procedure from and it never does anything. No exceptions at all, just nothing happens.

I started out calling this from Entity Framework 6 and that threw a very unhelpful exception which is why I moved to calling the stored procedure from .NET instead of EF (and this explains the funky EntityConnectionStringBuilder construct in my code which pulls out just the SQL connection string portion of the larger EF connection string).

The user I am connecting with to SQL Server via .NET is a member of the sysadmin role so I don't think it's a permission issue. Any thoughts from anyone?

private static void BackupDatabase(string dbName)
{
        try
        {
            var efcb = new EntityConnectionStringBuilder(Core.WPMMasterConnectionString);

            using (var conn = new SqlConnection(efcb.ProviderConnectionString))
            {
                conn.Open();

                using (var cmd = new SqlCommand("dbo.usp_backup_database", conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@DBName", dbName));
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (SqlException sqlex)
        {
            DisplayUtils.ShowLine(sqlex);
        }
        catch (Exception ex)
        {
            DisplayUtils.ShowLine(ex);
        }
}
5
  • 4
    Does this answer your question? How to execute a stored procedure within C# program You need to add { CommandType = CommandType.StoredProcedure } Commented May 23, 2021 at 1:33
  • Are you sure there is no exception? if the CommandType is wrong it will throw an error Commented May 23, 2021 at 7:11
  • Unless it's a very small database I'd expect an SqlException when the backup takes more than 30 seconds to complete and a timeout gets raised. You may wish to set a cmd.CommandTimeout value. Commented May 23, 2021 at 10:23
  • You should also try using the SqlParameter (string parameterName, System.Data.SqlDbType dbType, int size) constructor in case it's defaulting to nvarchar(1) and truncating your dbName. Commented May 23, 2021 at 10:26
  • It was the missing commandtype.storedprocedure, thanks for the help! Commented May 23, 2021 at 19:55

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.