SQL SERVER數(shù)據(jù)庫自動備份與還原方法

李光輝

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

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

本文介紹的數(shù)據(jù)庫自動備份與還原方法，在雙機(jī)服務(wù)器群集+微軟群集管理功能和鏡像雙機(jī)熱備+見證服務(wù)器兩種高可用性數(shù)據(jù)庫架構(gòu)中，平穩(wěn)應(yīng)用多年。

為方便描述，下文以主數(shù)據(jù)庫架構(gòu)為雙機(jī)服務(wù)器群集+微軟群集管理功能為例進(jìn)行介紹，數(shù)據(jù)庫服務(wù)器操作系統(tǒng)為Windows server2008 R2。數(shù)據(jù)庫版本為SQL Server 2008 R2。

1 數(shù)據(jù)庫備份自動還原設(shè)計

雙機(jī)服務(wù)器配置為SQL Server 故障轉(zhuǎn)移群集，保證主備節(jié)點故障時的自動切換，實現(xiàn)數(shù)據(jù)庫的高可用性。群集系統(tǒng)架構(gòu)如圖1。

主備數(shù)據(jù)庫數(shù)據(jù)同步流程如圖2。

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

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

2 數(shù)據(jù)庫備份自動還原實現(xiàn)

2.1 自動定時備份數(shù)據(jù)庫

在主數(shù)據(jù)庫系統(tǒng)中為每個數(shù)據(jù)庫建立維護(hù)計劃，維護(hù)計劃內(nèi)容含兩個任務(wù)：

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

（2）設(shè)置“清除維護(hù)”任務(wù)，完成對超過一定時間（如兩周）的備份文件進(jìn)行刪除。

圖1：數(shù)據(jù)庫架構(gòu)圖

圖2：數(shù)據(jù)同步流程圖

根據(jù)系統(tǒng)中數(shù)據(jù)庫的數(shù)量和系統(tǒng)業(yè)務(wù)情況，設(shè)置好每天維護(hù)計劃的數(shù)量和執(zhí)行次數(shù)，維護(hù)計劃的執(zhí)行時間，應(yīng)設(shè)定為每天系統(tǒng)空閑時，如我臺高清播出系統(tǒng)中設(shè)置了兩個維護(hù)計劃，每天中午13點和凌晨2 點各執(zhí)行一次。

2.2 復(fù)制備份文件至備份數(shù)據(jù)庫

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

（1）將主數(shù)據(jù)系統(tǒng)的備份文件復(fù)制至周期文件夾中。

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

xcopy 為復(fù)制文件命令，其中/y 參數(shù)指定復(fù)制時，不會提示以確認(rèn)要覆蓋現(xiàn)有目標(biāo)文件，/d:%date:~5,2%-%date:~8,2%-%date:~0,4% 指定只復(fù)制當(dāng)前系統(tǒng)日期的文件。

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

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

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

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

（3）復(fù)制當(dāng)日備份文件至當(dāng)日備份文件夾。

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

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

盤符:

cd 備數(shù)據(jù)庫備份文件目錄路徑當(dāng)日備份

進(jìn)入備數(shù)據(jù)庫備份文件所在目錄路徑。

del 數(shù)據(jù)庫名.bak

刪除數(shù)據(jù)庫文件。

rename *.bak 數(shù)據(jù)庫名.bak

重命名復(fù)制來的當(dāng)日數(shù)據(jù)庫備份文件名為數(shù)據(jù)庫名。

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

2.3 創(chuàng)建任務(wù)計劃程序，執(zhí)行批處理文件

在備數(shù)據(jù)庫系統(tǒng)操作系統(tǒng)中創(chuàng)建任務(wù)計劃程序，在創(chuàng)建任務(wù)窗口中進(jìn)行設(shè)置，在常規(guī)頁簽中設(shè)置計劃名，在觸發(fā)器頁簽中設(shè)置執(zhí)行時間和頻次，勾選啟用，執(zhí)行時間要確保為每天備份文件生成之后，在操作頁簽中設(shè)置批處理存放路徑，并將操作設(shè)定為“啟動程序”。

2.4 設(shè)置維護(hù)計劃，執(zhí)行T-SQL語句還原數(shù)據(jù)庫

在備數(shù)據(jù)庫系統(tǒng)中為每個數(shù)據(jù)庫建立維護(hù)計劃，維護(hù)計劃任務(wù)為“執(zhí)行T-SQL 語句”，每天按時用備份文件還原數(shù)據(jù)庫，特別注意的是計劃執(zhí)行的時間設(shè)置為復(fù)制任務(wù)完成之后。

T-SQL 語句如下：

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

GO

3 維護(hù)實踐中的幾個要點

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

3.1 日志清除

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

（1）新建2 個查詢，一個用于查詢?nèi)罩颈碛涗洈?shù)，另一個用于刪除日志表記錄；

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

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

（3）刪除記錄：

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

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

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

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

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

3.2 收縮數(shù)據(jù)庫文件

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

數(shù)據(jù)庫允許對每個文件進(jìn)行收縮，直至收縮到?jīng)]有剩余的可用空間為止?？梢宰詣踊蚴謩邮湛s。數(shù)據(jù)庫物理文件分為數(shù)據(jù)庫文件“*.mdf”和日志文件“*.ldf”。通常日志文件較大。此處以手動收縮為例進(jìn)行介紹。

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

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

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

3.3 數(shù)據(jù)庫群集故障應(yīng)急措施

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

如果主數(shù)據(jù)庫群集發(fā)生宕機(jī)，兩個節(jié)點均不能提供正常服務(wù)。需立即啟動應(yīng)急預(yù)案，啟用備數(shù)據(jù)庫：

（1）檢查備數(shù)據(jù)庫SQL 服務(wù)工作狀態(tài)，驗證數(shù)據(jù)庫中的數(shù)據(jù)為最近恢復(fù)的數(shù)據(jù)。

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

（3）客戶端重連數(shù)據(jù)庫或重啟客戶端軟件，恢復(fù)業(yè)務(wù)。

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

4 總結(jié)

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