Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- :: 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%
Add Comment
Please, Sign In to add comment