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);
        }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Don Bouchard
  • 69
  • 2
  • 7
  • 4
    Does this answer your question? [How to execute a stored procedure within C# program](https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) You need to add `{ CommandType = CommandType.StoredProcedure }` – Charlieface May 23 '21 at 01:33
  • Are you sure there is no exception? if the CommandType is wrong it will throw an error – Akshay G May 23 '21 at 07: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](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout) value. – AlwaysLearning May 23 '21 at 10:23
  • You should also try using the `SqlParameter (string parameterName, System.Data.SqlDbType dbType, int size)` [constructor](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.-ctor) in case it's defaulting to `nvarchar(1)` and truncating your dbName. – AlwaysLearning May 23 '21 at 10:26
  • It was the missing commandtype.storedprocedure, thanks for the help! – Don Bouchard May 23 '21 at 19:55

0 Answers0