SQLServer 多点及时备份技巧

为了保证数据库的安全性，我们都会规划数据库的容灾策略，包含本地备份、异地备份、raid。或者使用高可用性（如日志传送、镜像、复制等）进行异地容灾。因为 SqlServer 数据库的备份仅仅有一个备份策略（如完整-差异-日志），对某个数据库中，仅仅创建一个完整的策略，不要创建多个，否则备份链对不上。备份各在一方。对于备份。一般使用 完整备份+差异备份+日志备份，或者 完整备份+差异备份+日志传送，可是备份仅仅有一个文件。为了在异地也保存有同样的备份。SqlServer 有几种參数可设置多地保存。如 MIRROR TO ，COPY_ONLY ，但仅仅对完整备份有效。全部其它情况下，都使用 windows 命令复制到其它地方做冗余存储。

曾经也用过一种方法。拷贝N天内的数据到其它地方（參考 forfiles 和 xcopy 在windows下拷贝N天内更改过的文件），可是使用windows 作业的方法拷贝。并不及时。这里就介绍一种方法。及时异地存储到网络路径中，也就是数据库备份完毕后，马上把文件复制到异地中。

在一个目录中。有众多的备份文件，怎么筛选出刚刚备份出来的文件呢？

用 windows 命令批处理应该能够完毕，可是出来会太麻烦。那就在 SqlServer 寻找吧。~

SqlServer 中每一个备份都会有记录，备份的名称、路径、时间等待都能够从备份历史中找出。

以master 完整备份为例：

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


SELECT 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',1
reconfigure

exec sp_configure 'xp_cmdshell',1
reconfigure

对于异地包含，须要建立网络映射：

exec master.dbo.xp_cmdshell 'net use \IPyourPath "password" /user:IPuser'

终于的拷贝脚本例如以下，在SqlServer使用 xp_cmdshell 进行拷贝：

DECLARE @OldPath NVARCHAR(200)
DECLARE @NewPath NVARCHAR(100)
DECLARE @cmdSQL NVARCHAR(300)
SET @NewPath = N'\192.168.1.111master'
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 @cmdSQL
EXEC MASTER.DBO.XP_CMDSHELL @cmdSQL

命令完毕了。该放在哪里运行呢？当然是数据库备份作业的下一步了！

~ 哈哈 O(∩_∩)O 自言自语~~

打开维护计划。选择控制流“ 运行T-SQL语句”的任务，将上面的语句粘贴到里面中。作为系统数据库备份后的下一步。完毕。~