I have a procedure that runs a bkp from my database. If I run it through SqlServer the bkp is performed normally But when I call a trigger, another procedure or application bkp is not performed, the folder is created on the server, but the bkp file is not there. Any ideas:
This is the procedure:
USE [WebAdiantamento]
GO
/****** Object: StoredProcedure [dbo].[backup_DB_WebAdiantamento] Script Date: 07/15/2016 07:47:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[backup_DB_WebAdiantamento]
as
begin
--VARIAVEIS BKP
DECLARE @DATA VARCHAR(15)
DECLARE @PATH VARCHAR(100)
DECLARE @HORA INT
DECLARE @MINUTO INT
DECLARE @EXISTE_DIRETORIO BIT
SET @HORA = DATEPART(HOUR, GETDATE())
SET @MINUTO = DATEPART(MINUTE, GETDATE())
SET @DATA = replace(CONVERT(VARCHAR, GETDATE(), 103), '/', '_')+ '_' +CAST(@HORA AS VARCHAR)+CAST(@MINUTO AS VARCHAR)
SET @PATH = 'C:\Backup_Adiantamento\'+@DATA
--VARIAVEIS REFERENTE A VALIDACAO DO BKP
DECLARE @BKPEXECUTADOHOJE INT
DECLARE @OQUE_E_AGORA VARCHAR(3)
DECLARE @HORA_ULTIMOBKP INT
DECLARE @MOMENTO_BKP VARCHAR(3)
--VERIFICA SE JA TEVE BKP HOJE
SELECT @BKPEXECUTADOHOJE = COUNT(*)
FROM LOGBKPDIARIO WHERE 1=1
AND DATEADD(DD, DATEDIFF(DD, 0, DATA), 0) = DBO.TRUNC_DATE(GETDATE())
--VERIFICA SE A HORA AGORA E AM OU PM
IF DATEPART(HOUR, GETDATE()) >= 6 AND DATEPART(HOUR, GETDATE()) <= 12
SET @OQUE_E_AGORA = 'AM'
ELSE IF DATEPART(HOUR, GETDATE()) >= 13 AND DATEPART(HOUR, GETDATE()) <= 20
SET @OQUE_E_AGORA = 'PM'
ELSE
SET @OQUE_E_AGORA = 'XX'
--PEGA A HORA DO ULTIMO BKP EXECUTADO
IF @BKPEXECUTADOHOJE > 0
BEGIN
SELECT @HORA_ULTIMOBKP = MAX(HORA),
@MOMENTO_BKP = CASE WHEN MAX(HORA) >= 6 AND MAX(HORA) <= 12
THEN 'AM'
ELSE
CASE WHEN MAX(HORA) >= 13 AND MAX(HORA) <= 20
THEN 'PM'
ELSE 'XX'
END
END
FROM LOGBKPDIARIO
WHERE 1=1
AND DATEADD(DD, DATEDIFF(DD, 0, DATA), 0) = DBO.TRUNC_DATE(GETDATE())
END
--AGORA VAMO FAZER ACONTECER
--COMECA A PREPARAR O BKP
--CRIACAO DO CAMINHO/PASTA AONDE SERÁ SALVO
CREATE TABLE #FILE (FILE_EXIST BIT, FILE_DIRECTORY BIT, PARENT_DIRECOTRY_PARENT_EXIST BIT)
INSERT INTO #FILE EXEC master..xp_fileexist @PATH
SELECT @EXISTE_DIRETORIO = #FILE.PARENT_DIRECOTRY_PARENT_EXIST FROM #FILE
IF @EXISTE_DIRETORIO = 1
BEGIN
declare @str_criapasta varchar(100)
set @str_criapasta = 'md ' + @PATH
EXEC master..XP_CMDSHELL @str_criapasta
END
SET @PATH = @PATH + '\WEBADIANTAMENTO-FULL.BAK'
--TERMINOU
--EXECUTANDO SE NAO FOI FEITO NADA HOJE
IF @BKPEXECUTADOHOJE = 0
BEGIN
--INICIA O BKP
BACKUP DATABASE WEBADIANTAMENTO TO DISK = @PATH WITH INIT
--GRAVA O LOG
BEGIN TRANSACTION
INSERT INTO LOGBKPDIARIO (DATA, BKPOK, HORA, MINUTO) VALUES (GETDATE(), 'S - BKP 01', DATEPART(HOUR, GETDATE()), DATEPART(MINUTE, GETDATE()))
IF @@ERROR <> 0
ROLLBACK
ELSE
COMMIT
END
--SE JA FOI FEITO O BKP NA PARTE DA MANHA(AM)
-- E AGORA FOR (PM TARDE) EXECUTA O BKP DENOVO
ELSE
IF @OQUE_E_AGORA = 'PM' AND @MOMENTO_BKP = 'AM'
BEGIN
--INICIA O BKP
BACKUP DATABASE WEBADIANTAMENTO TO DISK = @PATH WITH INIT
--TERMINOU
--GRAVA O LOG
BEGIN TRANSACTION
INSERT INTO LOGBKPDIARIO (DATA, BKPOK, HORA, MINUTO) VALUES (GETDATE(), 'S - BKP 02', DATEPART(HOUR, GETDATE()), DATEPART(MINUTE, GETDATE()))
IF @@ERROR <> 0
ROLLBACK
ELSE
COMMIT
END
end
This is the result when I run inside the sql server: When done here I have the expected result, folder and file created as it should be.