[SQL Server] 利用batch 備份資料庫

備份資料庫除了利用內部 SQL Server Agent 的schedule job 外, 還可以透過指令檔進行. 這樣的好處是可以透過中央化管理的形式去管理排程工作.建立Batch 檔案並命名為 databaseBackup.bat.

echo off 
REM File name	: databaseBackup.bat
REM Author		: Ling
REM Description	: Backup SQL server in file format.
REM Syntax		: databaseBackup <<user_name>> <<password>> <<serverName>> <<databaseName>>

REM Initial settings.
set currentDateTime=%date:~6,4%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~3,2%
set userName=%1
set password=%2
set serverName=%3
set databaseName=%4

set BACKUPFILENAME=%databaseName%-%currentDateTime%.bak
set logFilePath=C:\DBBackup\log\%currentDateTime%_%serverName%_%databaseName%.log

echo %date% database backup start, file name: %BACKUPFILENAME%
echo %date% database backup start, file name: %BACKUPFILENAME% >> %logFilePath%
sqlcmd -U %userName% -P %password% -S %serverName% -Q "BACKUP DATABASE [%databaseName%] TO DISK = '%BACKUPFILENAME%' WITH FORMAT" -b >> %logFilePath%
if %ERRORLEVEL% EQU 0 (
	echo %date% backup success. 
	echo %date% backup success. >> %logFilePath%
	echo log located in %logFilePath%
) ELSE (
	echo %date% backup failure.
	echo %date% backup failure. >> %logFilePath%
	echo log located in %logFilePath%
	exit /b 1
)

值得一提是在指令sqlcmd 中, -b 是指將結果以errorlevel 傳回, 以確定結果能否順利執行, 否則全部都會得到0.

而 exit 中, /b <<error_level_number>> 結束執行時傳回的errorlevel. 以讓執行的program 知道其結果.