備份資料庫除了利用內部 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 知道其結果.
Leave a Reply