How to run a bat file using Agent SQL Server (with administrator rights)

Question

Please tell me, has anyone come across how to run a script / bat file as administrator through the SQL Server Agent (Microsoft SQL Server 2016)?

I create a task in the agent (you need to clean files older than 7 days in the directory on the server) -> I want to go into steps, I create PowerShell:

$folderPath = "C:\backup\MSSQL\Daily"
$cutoffDate = (Get-Date).AddDays(-7)

Get-ChildItem -Path $folderPath | Where-Object { $_.LastWriteTime -LT $cutoffDate } | Remove-Item -Force

I run it - it works, but it does not delete files, because I need to run it as administrator.

I thought it would be better to run through a file.

I created a .bat file for deletion (the name is DEL_OLD_BACKUP_TEST.bat), I threw a cleaning script into it (running it manually on the server as administrator works fine) -> I set the PowerShell task in the agent

Start-Process -FilePath  "C:/backup/MSSQL/DEL_OLD_BACKUP_TEST.bat" -Verb runas

Does not clean files, although it works "Process Exit Code 0. The step succeeded."

Do I need to run it under a specific user?

Answer 1

You can also use sql script to delete files.

CREATE PROCEDURE [dbo].[PurgeOldFiles]
    @DaysToKeep INT,
    @FolderPath NVARCHAR(255)
AS
BEGIN
    -- Enable xp_cmdshell
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;

    DECLARE @Cmd NVARCHAR(4000);
    SET @Cmd = 'FORFILES /P "' + @FolderPath + '" /S /M * /D -' + CAST(@DaysToKeep AS NVARCHAR(3)) + ' /C "CMD /C DEL @FILE"';
    EXEC xp_cmdshell @Cmd;
END

Create the above procedure in your db and run it through SQL Server Agent