Doing Backup DB from Stored Procedure works in local but no in production

Viewed 81

i need to backup my database by a clic button on a web app. I put the code in a SP which is called by the clic, it create a .bak and store it in a server folder. This works perfectly executing the SP directly in SQL SERVER, clicking the button in local web app, but no in production.

The code in local and production are the same.

I tried to do the BK in many ways:

  • backup database DATABASENAME to disk = @route
  • set @query = 'backup database DATABASENAME to disk = ' + '''' + @route + '''' exec(@query) Catching the info in a variable.
  • set @query = 'backup database DATABASENAME to disk = ' + '''' + @route + '''' exec @respuesta = sp_executesql @query

Again, all of these ways works in local but no in production.

Here is my SP:

ALTER procedure [dbo].[sp_IWBackupDatabase]
as
begin
declare @route nvarchar(500), @query nvarchar(1000), @respuesta int = 1
set nocount on
set @route = 'I:\SERVERFOLVER\BackupDB\DATABASENAMEFull.bak'

begin try
backup database DATABASENAME to disk = @route
set @respuesta = 0
end try
begin catch
set @respuesta = 1
end catch

select @respuesta Respuesta
end

Here is my call in with JS:

function BackupDatabase() {
    $.post('main.php', {
        action: 'BackupDatabase'
    }, function(e) {
        if (e.error) {
            if (e.r == "SQLSTATE[IMSSP]: The active result for the query contains no fields.") {
                Core.ShowNotific8('It WORKS', 'lime', false);
                DBWasBackUp = 1;
                $('#btn_CreateDBBK').attr('disabled', 'disabled');
            } else {
                Core.ShowNotific8('It NOT WORKS', 'ruby', false);
                DBWasBackUp = 0;
                $('#btn_CreateDBBK').removeAttr('disabled');
            }
        } else {
            if (e.r[0]['Respuesta'] == '0') {
                Core.ShowNotific8('It WORKS', 'lime', false);
                DBWasBackUp = 1;
                $('#btn_CreateDBBK').attr('disabled', 'disabled');
            } else {
                Core.ShowNotific8('It NOT WORKS', 'ruby', false);
                DBWasBackUp = 0;
                $('#btn_CreateDBBK').removeAttr('disabled');
            }
        }

    });
    return false;
}

Finally, here is the call from PHP:

function BackupDatabase()
{
$cnx = $this->connectSqlSrv():
$sth = $cnx->prepare("Exec sp_IWBackupDatabase");
$retval json_encode($retval);
}
1 Answers

It is likely permissions. When you are running the site under VS the user is running as your local windows user. When you deploy to IIS it is running as the application pool user. Check what user this is running under by going to IIS > Sites > Right Click The Site > Manage WebSite > Advanced Settings. That is the user it is running as and that user may need to have SQL access. If you need to add a new application pool look here:https://docs.microsoft.com/en-us/iis/configuration/system.applicationhost/applicationpools/add/#how-to

Related