How can i use sql script variable in a batch file

Question

I have an "SQL script", which backup database on local hard drive with current date in filename backup,example:

DECLARE @backuppath varchar(1000)

SELECT @backuppath = (SELECT 'C:\abat\delta_c_' + convert(varchar(500),GetDate(),112.bak')

BACKUP DATABASE [TEST_2] TO DISK=@backuppath

Also i have an Batch file, which run and execute my "SQL script". example:

@echo

sqlcmd -S .\SQLEXPRESS -i "c:\abat\myscript.sql"

if exist "C:\abat\@backuppath"(

Forfiles -p "C:\abat" -s -m *.BAK -d -7 -c "cmd /c del /q @path"

) else (

rem file doesn't exist

)

pause

How can i use sql script variable @backuppath in a batch file to verify successful creation of the backup file with the current date and execute next condition "forfiles"

Answer 1

Modify your SQL script:

set nocount on
DECLARE @backuppath varchar(1000);
SELECT @backuppath = (SELECT 'C:\abat\delta_c_' + convert(varchar(500),GetDate(),112.bak');
BACKUP DATABASE [TEST_2] TO DISK=@backuppath;
select @backuppath;

And your batch script. I'm not sure about your logic, but the changes should at least retrieve the value of @backuppath

@echo OFF
setlocal

for /f "delims=" %%A in (
  'sqlcmd -S .\SQLEXPRESS -h-1 -i "c:\abat\myscript.sql"'
) do set "@backupPath=%%A"

if exist "%@backupPath%" (
  Forfiles -p "C:\abat" -s -m *.BAK -d -7 -c "cmd /c del /q @path"
) else (
  rem file doesn't exist
)
pause