SQL SERVER數據庫自動備份與還原方法

李光輝

（江西廣播電視臺 江西省南昌市 330046）

SQL Sever 數據庫系統較Oracle、Sybase 等大型數據庫系統有易用性、易維護的優勢，多用于中小型系統中。在SQL Sever 數據庫系統應用中，主要是采用組合多種高可用技術的方案來提高數據庫系統的可靠性，如：雙機服務器群集+微軟群集管理功能或第三方故障檢測遷移軟件、鏡像雙機熱備+見證服務器等。此外，還需配置備份數據庫系統，如何安全可靠地實現主備數據庫系統之間的數據同步十分重要，實現的方式有多種，如配置微軟的發布/訂閱功能、編寫存儲過程等，本文詳細介紹了一種基于批處理和T-SQL語句實現SQL Server 數據庫自動備份與還原的方法，該方法簡單可靠。

本文介紹的數據庫自動備份與還原方法，在雙機服務器群集+微軟群集管理功能和鏡像雙機熱備+見證服務器兩種高可用性數據庫架構中，平穩應用多年。

為方便描述，下文以主數據庫架構為雙機服務器群集+微軟群集管理功能為例進行介紹，數據庫服務器操作系統為Windows server2008 R2。數據庫版本為SQL Server 2008 R2。

1 數據庫備份自動還原設計

雙機服務器配置為SQL Server 故障轉移群集，保證主備節點故障時的自動切換，實現數據庫的高可用性。群集系統架構如圖1。

主備數據庫數據同步流程如圖2。

要實現以上流程按時自動完成，需要對流程中的三步進行詳細的設計，其中第一步，通過建立“維護計劃”實現；第二步，通過編輯簡單的批處理命令實現，批處理程序每天按時由“任務計劃程序”自動運行；第三步，通過建立“維護計劃”，計劃中的任務為“執行T-SQL 語句”任務，通過T-SQL 語句RESTORE DATABASE 恢復數據庫。

由于SQL Server 故障轉移群集依賴于域環境，故需要配置域控服務器，需要注意的是，域控不可與SQL 數據庫軟件部署在同一臺服務器上，否則SQL 數據庫軟件將無法完成安裝，并且無法回退。為了安全可靠，域控應配置為主備模式，即域控需要使用兩臺服務器來承擔，一臺為主域控，一臺為備域控。兩臺主數據庫服務器共享磁盤陣列，磁盤陣列分為數據盤和仲裁盤，數據盤用于數據庫數據文件的存放，仲裁盤用于協調集群節點間的故障轉移，仲裁盤位于共享磁盤陣列中，能保證所有節點都能夠訪問到。備數據庫數據文件存在本機磁盤中。

2 數據庫備份自動還原實現

2.1 自動定時備份數據庫

在主數據庫系統中為每個數據庫建立維護計劃，維護計劃內容含兩個任務：

（1）設置“備份數據庫（完整）”任務，設置備份文件存放的目錄及文件夾，將此文件夾設置為共享，并設置訪問權限。勾選驗證備份完整性。

（2）設置“清除維護”任務，完成對超過一定時間（如兩周）的備份文件進行刪除。

圖1：數據庫架構圖

圖2：數據同步流程圖

根據系統中數據庫的數量和系統業務情況，設置好每天維護計劃的數量和執行次數，維護計劃的執行時間，應設定為每天系統空閑時，如我臺高清播出系統中設置了兩個維護計劃，每天中午13點和凌晨2 點各執行一次。

2.2 復制備份文件至備份數據庫

在備份數據庫中，建立名為“周期備份”和“當日備份”兩個文件夾，分別用來存放一段時間的備份文件和當日備份文件。在備份數據庫系統中編寫批處理程序，程序主要功能如下（以下藍色部分表示批處理語句）：

（1）將主數據系統的備份文件復制至周期文件夾中。

xcopy \主數據庫備份文件目錄路徑*.bak 備數據庫備份文件目錄路徑周期備份 /y /d:%month%-%day%-%year%

xcopy 為復制文件命令，其中/y 參數指定復制時，不會提示以確認要覆蓋現有目標文件，/d:%date:~5,2%-%date:~8,2%-%date:~0,4% 指定只復制當前系統日期的文件。

注意/d 指定的日期必須是“MM-DD-YYYY”的格式。

（2）保留一段時間的備份文件。

forfilеs /p 備數據庫備份文件目錄路徑周期備份 /m *.bak /d -15 /c "cmd /c del /f @path"

forfilеs 為文件處理命令，/p 指定目錄路徑，/m 指定查找文件名掩碼，/d -15 表示指定當前日期的15 日前，/c "cmd /c del /f @path"表示為目錄下每個文件執行強制刪除命令。

（3）復制當日備份文件至當日備份文件夾。

xcopy 備數據庫備份文件目錄路徑周期備份*.bak 備數據庫備份文件目錄路徑當日備份 /y /d:%month%-%day%-%year%

（4）刪除當日備份文件夾文件，重命名拷貝文件夾為數據庫名，因需要指定只復制當天生成的文件，此處拷貝時不可以使用copy命令來完成。

盤符:

cd 備數據庫備份文件目錄路徑當日備份

進入備數據庫備份文件所在目錄路徑。

del 數據庫名.bak

刪除數據庫文件。

rename *.bak 數據庫名.bak

重命名復制來的當日數據庫備份文件名為數據庫名。

將以上藍色部分命令的路徑部分替換為實際路徑，批處理程序即可運行。

2.3 創建任務計劃程序，執行批處理文件

在備數據庫系統操作系統中創建任務計劃程序，在創建任務窗口中進行設置，在常規頁簽中設置計劃名，在觸發器頁簽中設置執行時間和頻次，勾選啟用，執行時間要確保為每天備份文件生成之后，在操作頁簽中設置批處理存放路徑，并將操作設定為“啟動程序”。

2.4 設置維護計劃，執行T-SQL語句還原數據庫

在備數據庫系統中為每個數據庫建立維護計劃，維護計劃任務為“執行T-SQL 語句”，每天按時用備份文件還原數據庫，特別注意的是計劃執行的時間設置為復制任務完成之后。

T-SQL 語句如下：

RESTORE DATABASE [數據庫名]FROM DISK = N' 當 日備份路徑當日備份文件名.bak' WITH FILE = 1,MOVE N'數據庫名' TO N'備數據庫數據文件路徑數據庫名.mdf',MOVE N'數據庫名_log' TO N'備數據庫數據文件路徑數據庫名_log.ldf', NOUNLOAD,REPLACE,STATS = 10

GO

3 維護實踐中的幾個要點

在SQL server 群集數據庫的日常運維中，除對數據庫進行備份還原操作外，還需要進行如日志清除、數據庫文件收縮、應急等必要操作。

3.1 日志清除

系統長時間運行或系統設計等原因，可能導致數據庫中的某些表數據量過大，導致數據庫性能顯著下降，需要對數據庫日志表進行清理。在清理進行之前，需要提前備份數據庫，對待清除日志進行備份保留，以防排查問題需要查詢以前的數據。

（1）新建2 個查詢，一個用于查詢日志表記錄數，另一個用于刪除日志表記錄；

（2）查詢記錄數：select count(*) from Log_table

Log_table 是需查詢的表名，結果可以查看有多少行數據，如果數據量過多就需要刪除數據。

（3）刪除記錄：

1.部分刪除表中日志記錄：delete top(3000000) from Log_table

表示刪除Log_table 表前3000000 行數據記錄，用于部分刪除表中日志記錄，因為刪除過程是從表中一行一行刪除，并且將該刪除操作記入日志中保存以便進行回滾操作，刪除過程較緩慢。

2.全部刪除表中日志記錄：truncate table Log_table

表示刪除表中全部日志記錄，但表結構保持不變。因為該方式采用直接釋放數據頁的方式來刪除記錄，刪除速度快，效率高，但操作立即生效，不能回滾。

（4）選中要操作的數據庫，執行②中的查詢記錄語句，如果查詢結果行數過大，如達百萬級，則需考慮執行刪除操作，如需保留部分日志記錄，則需要使用delete 語句進行操作，不能夠使用truncate 進行全部刪除，需注意在使用delete 語句時，一次執行刪除數量不宜太大，如不應超過一百萬，數量越大，執行時間越長，應盡量避開業務高峰期操作。

3.2 收縮數據庫文件

由于SQL server 對數據庫空間的分派采取的是“先分派、后使用”的方式，所以數據庫在使用的過程中就可能會存在多余的空間，在一定程度上造成存儲空間的浪費，數據庫文件增長到一定大小后，需對數據庫文件進行收縮操作。

數據庫允許對每個文件進行收縮，直至收縮到沒有剩余的可用空間為止。可以自動或手動收縮。數據庫物理文件分為數據庫文件“*.mdf”和日志文件“*.ldf”。通常日志文件較大。此處以手動收縮為例進行介紹。

（1）登錄數據庫管理工具，在需要操作的數據庫上右健點擊，在彈出菜單中選擇依次選擇“任務”/“收縮”/“數據庫”。若只要收縮單個數據庫文件，在菜單中選擇依次選擇“任務”/“收縮”/“文件”。

（2）在“收縮數據庫”對話框中，如可用空間百分比大于0 時，收縮可釋放空間，點擊確定可釋放空間。收縮幅度不應太大，如每次500M 大小，一次收縮空間過大，會導致磁盤無法響應。

如果收縮未能有效減少數據庫文件所占用的空間，可考慮對數據庫進行日志文件重建操作。此操作需要分離數據庫，故需要中斷數據庫的連接。

3.3 數據庫群集故障應急措施

在數據庫出現異常時，不能任意重啟或斷電，一般應急手段均是啟用備份文件。因此要求系統維護人員每天檢查自動備份批處理狀態，確保其每天按時成功執行，查看備份存儲路徑下的備份文件是否定時生成，生成時間和文件大小是否正常，并每日定期檢查備數據庫服務器的業務數據庫是否按時成功還原。

如果主數據庫群集發生宕機，兩個節點均不能提供正常服務。需立即啟動應急預案，啟用備數據庫：

（1）檢查備數據庫SQL 服務工作狀態，驗證數據庫中的數據為最近恢復的數據。

（2）修改所有客戶端軟件的數據庫連接地址為備數據庫服務器地址。

（3）客戶端重連數據庫或重啟客戶端軟件，恢復業務。

一旦事故發生，在客戶端較多的情況下，以上方法至少需要10 分鐘完成。經過我們多次實測，可以通過直接將備數據庫服務器的IP 地址，改為數據庫群集的虛擬IP 地址，讓備數據庫直接頂替數據庫群集工作，省去了以上（2）和（2）步的操作，3 分鐘以內就可以恢復業務。

4 總結

本文介紹的基于批處理和T-SQL 語句的SQL Sever 備份數據庫自動與還原方法，經濟、簡單、實用，已應用在我臺的兩套高清播出系統中，至今已平穩可靠運行近6年，極大地減輕了運維人員工作壓力。