BKP Sqlserver does not run when executed by a trigger(Trigger)

Question

I have a trial that runs a bkp from my database. If I run it by Sqlserver the bkp is normally performed But when I call for a Rigger, another trial or application the bkp is not realized, the folder is created on the server, but the bkp file is not there. Any idea:

This is the last:

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 running within sql server: When done here I have the expected result, folder and file created as it is to be.

Answer 1

Jeterson, you need to know what the backup_DB_WebAdministration does, what is its behavior performed outside of Rigger’s context and also with which user it is running. More information.