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