backup database for SQL Server via sqlcmd

sql_auto_bak.cmd

:: Name:     sql_auto_bak.cmd
:: Purpose:  backup database for SQL Server via sqlcmd
:: Author:   ettingshausen
:: Revision: 2018/1/24 - initial version
::

@ECHO OFF
SETLOCAL ENABLEEXTENSIONS
SET parent=%~dp0
SET interactive=0
SET sql_script="%parent%script.sql"
SET time_wrapper=%TIME: =0%
SET time_local=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%time_wrapper:~0,2%%time_wrapper:~3,2%%time_wrapper:~6,2%
ECHO %COMSPEC% | FINDSTR /L %CMDCMDLINE% >NUL 2>&1
IF %ERRORLEVEL% == 0 SET interactive=1


SET target_path=D:\DATA\BACKUP
SET database=PT_INS_HA_UPGRADE0112
SET server="localhost"
SET username="sa"
SET password="1"



:: create folder if not exist
if not EXIST %target_path% ( MD %target_path%)

:: generate sql
ECHO declare @stdatetime NVARCHAR(30)='%time_local%' > %sql_script%
ECHO declare @DATA_BASE NVARCHAR(30)=N'%database%' >> %sql_script%
ECHO declare @BACK_NAME NVARCHAR(600)=@DATA_BASE+'_'+@stdatetime >> %sql_script%
ECHO declare @BACK_PATH NVARCHAR(600)=N'%target_path%\\'+@BACK_NAME+'.bak' >> %sql_script%
ECHO BACKUP DATABASE @DATA_BASE TO  DISK = @BACK_PATH >> %sql_script%
ECHO WITH  RETAINDAYS = 30, NOFORMAT, NOINIT,  NAME = @BACK_NAME, SKIP, REWIND, NOUNLOAD,  STATS = 10; >> %sql_script%
ECHO GO >> %sql_script%

:: execute script
sqlcmd -S %server% -U %username% -P %password% -i %sql_script%

:: delete script
DEL %sql_script%

IF "%interactive%"=="0" PAUSE
EXIT /B %ERRORLEVEL%