[MS-SQL] 백업 스크립트

@ECHO OFF
CHCP 65001 1> NUL 2> NUL
REM #################################################################
REM #
REM # Microsoft SQL 백업 스크립트
REM #
REM #################################################################

REM #################################################################
REM # 명명된 파이프 오류 발생 시...
REM # SQLServerManager12.msc 실행 후 SQL Server 네트워크 구성 > MSSQLSERVER에 대한 프로토콜 > 명명된 파이프 (사용)
REM #
REM # 인스턴스 명 알아내기 (출력값의 왼쪽 문자열이 인스턴스 명)
REM # REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
REM #################################################################

REM #################################################################
REM #
REM # 변수 정의
REM #
REM #################################################################
SET "INSTANCE_NAME=MSSQLSERVER"
SET "DB_NAME=webinspect_1910"
SET "BACKUP_DIR=C:\db_backup"
SET "SCHTASKS_NAME=db_backup"
SET "TEMP_SQL=%TEMP%\__tmp__.sql"

REM #################################################################
REM #
REM # 윈도우 스케줄러 등록
REM #
REM #################################################################
ECHO SCHTASKS /DELETE /F /TN "%SCHTASKS_NAME%"
REM ------------------------------------------------------------------
REM 매주 일요일 오전 01:00에 실행 (관리자 권한으로 실행)
REM ------------------------------------------------------------------
SET SCHTASKS_RUN=%CD%\%0"
ECHO SCHTASKS /CREATE /F /SC WEEKLY /D SUN /ST 01:00 /TN "%SCHTASKS_NAME%" /TR "%SCHTASKS_RUN%"

REM #################################################################
REM #
REM # 백업 SQL 문을 작성한다.
REM #
REM #################################################################
ECHO DECLARE @name NVARCHAR(256) -- database name > %TEMP_SQL%
ECHO DECLARE @path NVARCHAR(512) -- path for backup files >> %TEMP_SQL%
ECHO DECLARE @fileName NVARCHAR(512) -- filename for backup >> %TEMP_SQL%
ECHO DECLARE @fileDate NVARCHAR(40) -- used for file name >> %TEMP_SQL%
ECHO. >> %TEMP_SQL%
ECHO SET @name = '%DB_NAME%' >> %TEMP_SQL%
ECHO. >> %TEMP_SQL%
ECHO -- specify database backup directory >> %TEMP_SQL%
ECHO SET @path = '%BACKUP_DIR%\' >> %TEMP_SQL%
ECHO. >> %TEMP_SQL%
ECHO -- specify filename format >> %TEMP_SQL%
ECHO SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','') >> %TEMP_SQL%
ECHO. >> %TEMP_SQL%
ECHO SET @fileName = @path + @name + '_' + @fileDate + '.bak' >> %TEMP_SQL%
ECHO BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, INIT, NAME = @fileDate, SKIP, REWIND, UNLOAD, STATS=10 >> %TEMP_SQL%
ECHO. >> %TEMP_SQL%
ECHO GO >> %TEMP_SQL%

REM #################################################################
REM #
REM # 백업 SQL을 실행 시킨다.
REM #
REM #################################################################
REM SqlCmd -S "HP-COMPAQ6000\MSSQLSERVER"
REM SqlCmd -E -S "%COMPUTERNAME%\%INSTANCE_NAME%" -l 30 -i "%TEMP_SQL%"
SqlCmd -E -S "%COMPUTERNAME%" -l 30 -i "%TEMP_SQL%"
DEL /Q /F "%TEMP_SQL%"