SQLServer 多點及時備份技巧，sqlserver多點備份

SQLServer 多點及時備份技巧，sqlserver多點備份

為了保證資料庫的安全性，我們都會規劃資料庫的容災策略，包括本地備份、異地備份、raid，或者使用高可用性（如 記錄傳送、鏡像、複製等）進行異地容災。由於 SqlServer 資料庫的備份只有一個備份策略（如 完整-差異-日誌），對某個資料庫中，只建立一個完整的策略，不要建立多個，否則備份鏈對不上，備份各在一方。對於備份，一般使用完整備份+差異備份+記錄備份，或者 完整備份+差異備份+記錄傳送，但是備份只有一個檔案。為了在異地也儲存有相同的備份，SqlServer 有幾種參數可設定多地儲存，如 MIRROR TO ，COPY_ONLY ，但只對完整備份有效。所有其他情況下，都使用 windows 命令拷貝到其他地方做冗餘儲存。

以前也用過一種方法，拷貝N天內的資料到其他地方（參考 forfiles 和 xcopy 在windows下拷貝N天內更改過的檔案），但是使用windows 作業的方法拷貝，並不及時。這裡就介紹一種方法，及時異地儲存到網路路徑中，也就是Database Backup完成後，立即把檔案拷貝到異地中。

在一個檔案夾中，有眾多的備份檔案，怎麼篩選出剛剛備份出來的檔案呢？ 

用 windows 命令批處理應該可以完成，但是出來會太麻煩。那就在 SqlServer 尋找吧！~

SqlServer 中每個備份都會有記錄，備份的名稱、路徑、時間 等待都可以從備份曆史中找出。

以master 完整備份為例：

SELECT TOP(1) media_set_id ,nameFROM msdb.dbo.backupset WHERE database_name = 'master' and type = 'D'ORDER BY backup_finish_date DESCSELECT physical_device_name FROM msdb.dbo.backupmediafamily WHERE media_set_id = 2048

這些備份是維護計劃自動備份的，每個備份組中只有一個備份檔案，這就可以唯一確定一個檔案了！~

順便說明一下，backupset 中的 name 有時與實際的物理檔案名稱不匹配，所以不用該名字。但是 backupset 中有時間資訊，就按備份完成時間排序取第一條最新的。

註：backupset 中的欄位 type 表示不同的備份類型，D = 資料庫; I = 差異資料庫 ;L = 日誌 。（參考backupset）

因此，就可以確定 master 最新的完整備份檔案了！

SELECT physical_device_name FROM msdb.dbo.backupmediafamily where media_set_id =(SELECT TOP(1) media_set_id FROM msdb.dbo.backupset WHERE database_name = 'master' and type = 'D'ORDER BY backup_finish_date DESC)

為了能在 SqlServer 中更方便管理，檔案的拷貝也在資料庫中執行，需要啟用系統命令 xp_cmdshell。

exec sp_configure 'show advanced options',1reconfigureexec sp_configure 'xp_cmdshell',1reconfigure

對於異地包括，需要建立網路映射：

exec master.dbo.xp_cmdshell 'net use \\IP\yourPath "password" /user:IP\user'

最終的拷貝指令碼如下，在SqlServer使用 xp_cmdshell  進行拷貝：

DECLARE @OldPath NVARCHAR(200)DECLARE @NewPath NVARCHAR(100)DECLARE @cmdSQL NVARCHAR(300)SET @NewPath = N'\\192.168.1.111\master\'SELECT @OldPath = physical_device_name FROM msdb.dbo.backupmediafamily WHERE media_set_id =(SELECT TOP(1) media_set_id FROM msdb.dbo.backupset WHERE database_name = 'master' and type = 'D'ORDER BY backup_finish_date DESC)SET @cmdSQL = N'xcopy "'+@OldPath+'" "'+@NewPath+'" /y '--SELECT @cmdSQLEXEC MASTER.DBO.XP_CMDSHELL @cmdSQL

命令完成了，該放在哪裡執行呢？ 當然是Database Backup作業的下一步了！~ 哈哈 O(∩_∩)O 自言自語~~

開啟維護計劃，選擇控制流程“ 執行T-SQL語句”的任務，將上面的語句粘貼到裡面中，作為系統資料庫備份後的下一步，完成！~

執行看看！~這樣備份就比較快了！~（同樣試試其他資料庫的 完整備份+差異備份+記錄備份）