2010/07/29

[SQL Server] 建立 SQL Server Express 的定期自動備份

在 SQL Server 的 Express 版本中, 沒有自動備份的功能可使用.
一般備份就分成兩種方式:
  1. 透過 Management Studio Express 進行手動備份.
  2. 自行撰寫 T-SQL 的 Script, 或是寫程式去呼叫 T-SQL 的備份指令, 進行資料庫備份.

以下使用第二種方式, 並利用 Windows 的排程作業, 達到定期備份資料庫的作業.
需求:
  • 每日定期備份 LYTDB 和 LYTDB00 這兩個資料庫.
  • 將資料庫的備份檔放在 D:\Database_Backup\ 目錄內.
設計步驟如下:
  1. 建立一個 T-SQL 的備份 sql 檔 (例如: backup.sql), 並將該檔案放在 C:\ 內.
    USE master
    GO
    DECLARE @backupTime VARCHAR(20)
    DECLARE @sqlCommand NVARCHAR(1000)
    --設定檔名的執行時間.例如以下的@backupTime將會是200904221156(yyyyMMddHHmm)
    --此值可以視需求進行調整,如果是每小時備份,就只要2009042211(yyyyMMddHH)
    SELECT @backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112)
             +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
    --設定LYTDB資料庫的備份命令
    --可視需要修改備份檔存放的位置
    SET @sqlCommand = 'BACKUP DATABASE LYTDB TO DISK=''D:\Database_Backup\LYTDB_'
                      + @backupTime+'.bak'''
    EXECUTE sp_executesql @sqlCommand  
    --設定LYTDB00資料庫的備份命令
    --可視需要修改備份檔存放的位置
    SET @sqlCommand = 'BACKUP DATABASE LYTDB00 TO DISK=''D:\Database_Backup\LYTDB00_'
                      + @backupTime+'.bak'''
    EXECUTE sp_executesql @sqlCommand  
    GO
  2. 撰寫一個執行上一步 backup.sql 檔的 bat 檔(例如: backup_database.bat), 並放在與 backup.sql 相同的目錄中.
    內容只有一行:
    sqlcmd -S . -i c:\backup.sql
    sqlcmd 相關參考如下: http://msdn.microsoft.com/zh-tw/library/ms170572.aspx
  3. 在 Windows 中建立一個排程作業:
    1. [開始] -> [所有程式] -> [附屬應用程式] -> [系統工具] -> [排定的工作].
    2. Double Click [新增排定的工作] 後, 按下 [下一步].
    3. 按下 [瀏覽], 會出現選取程式的畫面 -> 選取 c:\ backup_database.bat 按下 [開啟], 最後按下 [下一步] 進行下一個步驟.
    4. 選取 [每日] -> 按下 [下一步].
    5. 開始時間選擇 [下午 12:30] -> 執行方式選 [每天] -> 開始日期維持預設或另外設定 -> 按下 [下一步].
    6. 輸入執行此程式的帳號 (用 administrator 帳號) -> 按下 [下一步].
    7. 勾選 [按下[完成]後開啟這項工作的進階內容] -> 按下 [完成].
    8. 在進階頁面中檢視各項設定是否無誤, 記得 [工作] 頁籤中的 [登入後才執行]要取消勾選, [啟用] 的選項要勾選.
    9. 如果一天內要備份多次 (例如多一個上午1點的備份), 可在 [排程] 頁籤中, 勾選 [顯示多項排程] -> 在上方按下 [新增] -> 設定工作排程為 [每天], 開始時間為 [上午01:00] -> 按下 [套用].
    10. 另外, 在 [設定] 頁籤中, 記得取消勾選 [在x小時後停止排定的工作] -> 按下 [確定]. (若有特殊需求, 此項可跳過, 並維持勾選)
    最後會在排定的工作中看到一個新增的工作項目, 可在該項目上按滑鼠右鍵, 選執行.
    執行後會在 D:\Database_Backup\ 產生 LYTDB_yyyyMMddHHmm.bak 與 LYTDB00_yyyyMMddHHmm.bak 兩個備份檔.
    (yyyyMMddHHmm 表示年月日時分, 例如: 200904221156)
透過上述的排程作業, 定時呼叫 backup_database.bat 這個批次作業, 並執行 backup.sql 中的備份作業, 就可達到定期備份的目的.

2 則留言:

匿名 提到...

不知備份前能否順道簡併資料庫之Log ?

yilinliu 提到...

你可以參考: http://support.microsoft.com/kb/907511/zh-tw
在備份的script前先用 DBCC SHRINKFILE 做log檔的壓縮.