# 如何透過 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 ---------------------------------------------------------------------------

**參考資料**

> Batch File Command Reference for Windows 2000  
> [http://labmice.techtarget.com/articles/batchcmds.htm](http://labmice.techtarget.com/articles/batchcmds.htm)
> 
> Batch FOR loops  
> [http://www.robvanderwoude.com/index.html](http://www.robvanderwoude.com/index.html)
> 
> % (variable)
> 
> %0          代表正在執行的這支 Batch 檔名  
> %1 to %9    代表傳入的指令列參數