如何透過 SQLCMD 程式和 T-SQL 指令備份 SQL 資料庫

以下共有兩支程式 BackupAllDB.bat 與 BackupScript.bat，最後還有批次檔的參考網址與部分說明。

BackupAllDB.bat 用來定義你要備份哪幾個資料庫，需呼叫 BackupScript.bat 執行備份動作！

BackupScript.bat 用來執行備份的動作

檔案如下：


BackupAllDB.bat
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

@echo off

set DBList=Northwind DBname2 DBName3
for %%a IN (%DBList%) DO (
CALL BackupScript.bat %%a
)

echo ----------------
echo 資料庫備份檔完成
echo ----------------

pause


BackupScript.bat
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

@echo off
::
:: 預設的 %BackupFile% 為「一天備份一次」的檔名
::

::■■■■■■■■■■■■■■■■■
:: 基本需求
::■■■■■■■■■■■■■■■■■
::
:: 1. 要安裝 7-zip 壓縮軟體(可不裝)
:: 2. 預設資料庫備份目錄夾在 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup (此為SQLExpress預設目錄，可修改)
:: 3. 資料庫備份目錄夾必須可讓 SQLExpress / SQL 2005 的服務執行帳戶有寫入權限！
:: 4. 預設是使用「信任式連線」
::
::■■■■■■■■■■■■■■■■■
:: 參數設定
::■■■■■■■■■■■■■■■■■

:: 備份資料夾目錄 ( 最後「不要」加上斜線 )
set BackupDir=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
:: 封存資料夾
set ArchiveDir=\\SomeOtherNetworkShare\SQLDBBackup
:: 資料庫名稱 ( 由指令參數傳入 )
set DBName=%1
:: 資料庫位址
set DBHost=(local)\SQLExpress
:: 壓縮檔(7-zip)位址
set ZipProgram="c:\Program Files\7-Zip\7z.exe"

::■■■■■■■■■■■■■■■■■
:: 取得日期時間
::■■■■■■■■■■■■■■■■■

:: Setting environment variables with todoy's date values
for /f "tokens=1-4 delims=-/ " %%i IN ('date /t') DO (
set year=%%i
set month=%%j
set day=%%k
)

for /f "tokens=1-3 delims=:" %%i IN ('time /t') DO (
set hour=%%i
set minute=%%j
)

for /f "tokens=1 delims= " %%i IN ("%hour%") DO (
set hour=%%i
)

::■■■■■■■■■■■■■■■■■
:: 設定備份檔名
::■■■■■■■■■■■■■■■■■

:: 備份的檔名
set BackupFile=%BackupDir%\%DBName%_%year%-%month%-%day%.bak

::■■■■■■■■■■■■■■■■■
:: 執行備份
::■■■■■■■■■■■■■■■■■

IF "%DBName%" NEQ "" goto ExecuteBackup
echo ■■■■■■■■■■■■■■■■■■
echo ★★★★★未指定資料庫名稱★★★★★
echo ■■■■■■■■■■■■■■■■■■
pause
exit 0

:ExecuteBackup

echo === 備份 %DBName% 資料庫
echo ---------------------------------------------------------------------------

echo 執行備份到 %BackupDir%
::echo sqlcmd -S %DBHost% -E -Q "BACKUP DATABASE [%DBName%] TO DISK='%BackupFile%'"
sqlcmd -S %DBHost% -E -Q "BACKUP DATABASE [%DBName%] TO DISK='%BackupFile%'"

IF EXIST %ZipProgram% goto CheckBackupFile
echo ■■■■■■■■■■■■■■■■■
echo ★★★★★備份資料庫失敗★★★★★
echo ■■■■■■■■■■■■■■■■■
pause
exit 0
:CheckBackupFile

::■■■■■■■■■■■■■■■■■
:: 壓縮資料庫備份檔 ( 7z 格式 )
::■■■■■■■■■■■■■■■■■

IF NOT EXIST %ZipProgram% goto ZipBackupFile
:: 7z a -mx=9 "%year%-%month%-%day%-%hour%-%minute%.7z" %workspace%
echo ----------------
echo 壓縮資料庫備份檔
%ZipProgram% a -mx=9 "%BackupFile%.7z" "%BackupFile%" > nul
:ZipBackupFile

::■■■■■■■■■■■■■■■■■
:: 刪除資料庫備份檔
::■■■■■■■■■■■■■■■■■

set ZipBackupFile=%BackupFile%.7z
::echo %ZipBackupFile%

IF NOT EXIST "%ZipBackupFile%" goto DeleteBackupFile
echo ----------------
echo 刪除資料庫備份檔(僅留下壓縮檔即可)
del "%BackupFile%"
:DeleteBackupFile

::■■■■■■■■■■■■■■■■■
:: 搬移資料庫備份檔到封存資料夾
::■■■■■■■■■■■■■■■■■

if "%ArchiveDir%" EQU "" goto MoveToArchiveEnd

IF NOT EXIST "%BackupFile%.7z" goto NoZippedBackupFile
echo ----------------
echo 搬移資料庫備份檔到封存資料夾 "%ArchiveDir%"
move "%BackupFile%.7z" "%ArchiveDir%"
:NoZippedBackupFile

IF NOT EXIST "%BackupFile%" goto NoBackupFile
echo ----------------
echo 搬移資料庫備份檔到封存資料夾 "%ArchiveDir%"
move "%BackupFile%" "%ArchiveDir%"
:NoBackupFile

:MoveToArchiveEnd

echo ---------------------------------------------------------------------------

%0 代表正在執行的這支 Batch 檔名
%1 to %9 代表傳入的指令列參數