2016年5月17日 星期二

SQL 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:\ 內.
    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    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 中的備份作業, 就可達到定期備份的目的.
參考二
出處
SQL Server Express 為免費版的 SQL Server，並未支援以 SQL Server Agent 來設定自動備份資料庫，但可以透過Windows排定的工作呼叫SqlCmd指令來執行事先建立好的TSQL敘述達到類似SQL Server Agent的功能。

1. 建立 backup_time.sql

新增一純文字文件內容如下，其中 "POS" 字串請自行修改為資料庫名稱，然後儲存後將檔名更改為 "backup_time.sql"

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 [POS] TO DISK=''D:\POS_' + @backupTime+'.bak'''
EXECUTE sp_executesql @sqlCommand
GO

 2. 建立 backup_database.bat

新增一純文字文件內容如下，請自行修改其中的帳號密碼，然後將檔名更改為 "backup_database.bat"

sqlcmd -S .\sqlexpress -U 帳號 -P 密碼 -i "backup_time.sql"

最後透過 WINDOWS 排程設定自動執行批次檔即可
張貼者： 於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)