Sql-server – T-SQL script doesn’t delete files when run as an agent job – does if run manually

sql-server-2008-r2sql-server-agent

I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story – we're using a suppliers R1Soft for backup and it won't allow us to exclude folders when we're talking about ~1M files).

The idea behind the script is that it:

Grabs the last backup location from SQL Server
Deletes the file from the separate drive
Runs Ola Hallengrens script to run a backup
Grabs the last backup location again (so now it's the one just made)
Copies that file to the separate drive

The problem that i'm having is that everything runs ok if i'm running the agent job manually, but when it comes to have the job run as scheduled, it does the copy part at the end, but doesn't do the initial delete – so all the old files stay on the drive and have to be deleted manually.

There's nothing in the logs that indicate what could be happening (no failures or error messages). I'm assuming that it's a permissions issue – what user does the agent job run as when scheduled? Maybe it's the user that I'm logged in as thats used when running it manually?

Either that, or is there a better way of doing it? I don't really want to run the backup twice as there's around 1TB of data to back up.

Best Answer

The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user.

That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error information back, consider running this as one of your job steps (should/has to be a one liner for CmdExec to work right):

cmd /e:on /c "forfiles /p "c:\MYDATABASEBACKUPS" /s /m *.BAK /d -2 /c "cmd /c del @path" 2>&1 |  findstr /V /O /C:"ERROR: No files found with the specified search criteria."2>&1 | findstr ERROR&&EXIT 1||EXIT 0"&exit %errorlevel%

This script purges backups based on how old they are in number of days. The /d -2 tells the script to keep two days worth. Change 2 to whatever number you'd like to keep.

Also, change c:\MYDATABASEBACKUPS to your backup location. Change *.BAK to the backup extension of your backup files. The "fanciness" of this script is because I get rid of the false positive "No files found"--because sometimes there won't be files to find.

Good luck with your scripting!

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Did you use RAISERROR with severity 16?
This is "user defined error" and should be picked up by SQL Server agent

Did you set the job step to retry (Retry Interval and Retry Attempts)

Finally, is the error terminating (severity 20) such that the CATCH block isn't hit?

Sql-server – Check if SQL Agent job is being executed manually

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job. If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )


        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END

Best Answer

Related Solutions

Sql-server – Make SQL Server Agent retry failed script with Try/Catch

Sql-server – Check if SQL Agent job is being executed manually

Related Question