免費論壇 繁體 | 簡體
Sclub交友聊天~加入聊天室當版主
分享
Board logo

標題: SQL 使用SQLCMD備份 [打印本頁]

作者: mhfo    時間: 2014-4-15 14:48     標題: SQL 使用SQLCMD備份

參考

以下共有兩支程式  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%"
eleteBackupFile

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

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

Batch FOR loops
http://www.robvanderwoude.com/index.html

% (variable)

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

參考


執行步驟為
1. 寫好備份SQL語法
2.寫好執行T-SQL語法之批次檔
3.設定執行批次檔
4.執行定期刪除過期備份檔案之批次檔
1. 寫好備份SQL語法
DECLARE @DBName varchar(30)
Declare @BkFile  Varchar(200)
Declare @BkFolder Varchar(200)
--設置需要備份的資料庫
SET  @DBName='TEST_DB'
--設定存放備份文件的文件夾
SET  @BkFolder='D:\backUp_File\backup_data'
--備份檔文件名
SET @BkFile=@BkFolder +  '\'+@DBName + '.BAK'
--執行備份
BackUp Database @DBName To  Disk=@BkFile
寫完之後 另存為 BackUp.sql
2.寫好執行T-SQL語法之批次檔
# sqlcmd -S  【資料庫登入 EX: 127.0.0.1\DB】 -E -i 【寫好之備份語法之SQL檔 EX: BackUp.sql】
sqlcmd -S  127.0.0.1\DB -E -i D:\backUp_File\BackUp.sql
#備份完成後,壓縮.BAK檔,以免太大佔空間
set  TODAY=%DATE:/=%
set THISDATE=%TODAY:~0,8%
path C:\Program Files  (x86)\7-Zip
7z a -tzip D:\backUp_File\backup_data\dbzip_%THISDATE%.zip  D:\backUp_File\backup_data\TEST_DB.BAK
#執行完本機備份後,再備一份到異地
copy  D:\backUp_File\backup_data\dbzip_%THISDATE%.zip K:\
del  D:\backUp_File\backup_data\TEST_DB.BAK
3.設定執行批次檔
執行WINDOWS或是備份軟體,去執行寫好的批次檔
4.執行定期刪除過期備份檔案之批次檔
forfiles  /P D:\xxx\ /M *.ZIP /D -15  /C "cmd /c del @path"

FORFILES [/P pathname] [/M searchmask] [/S]
[/C command] [/D [+ | -] {yyyy/MM/dd | dd}]
描述:
選取一個檔案  (或一組檔案),並在檔案上執行命令。
參數清單:
/P pathname 表示要開始搜尋的路徑。
預設資料夾是目前的目錄  (.)。
/M searchmask 根據  searchmask 搜尋檔案。
預設的 searchmask 是 '*'。
/S 指示 forfiles 遞迴搜尋子目錄。例如 "DIR /S"。
/C command 表示每個檔案要執行的命令。
命令字串應該包含在雙括號之間。
預設的命令是 "cmd /c echo @file"。
下列變數可以使用於命令字串:
@file -  傳回檔案的名稱。
@fname - 只傳回檔案的名稱,不傳附檔名。
@ext - 只傳回檔案的附檔名。
@path -  傳回檔案的完整路徑。
@relpath - 傳回檔案的相對路徑。
@isdir - 如果檔案類型是目錄的話,
傳回  "TRUE",如果是檔案的話,傳回 "FALSE"。
@fsize - 傳回檔案的大小,單位是位元組。
@fdate -  傳回檔案上次修改的日期。
@ftime - 傳回檔案上次修改的時間。
要在命令列中包含特殊字元的話,請使用 0xHH 格式的十六
進位字元碼 (例如 0x09 代表 tab)。
內部的 CMD.exe 命令應該以 "cmd /c" 執行。
/D date 選取檔案上次修改的時間大於、等於 (+) 或小於或等於 (-)、
使用 "yyyy/MM/dd"  格式表示指定的日期;
或選取檔案日期上次修改的時間大於或等於 (+)
目前的日期再加上"dd" 天、小於或等於 (-) 目前的日期減去 "dd"  天。
有效的 "dd" 可以是介於0 - 32768 之間的數字。
如果未指定,"+" 將被視為預設符號。
/? 顯示這個說明訊息。

參考連結




歡迎光臨 魚骨頭的雲端圖書館 (http://mhfo.hotbbs.info/) Powered by Discuz! 7.0.0