SQLServer 多点及时备份技巧

因此，就可以确定 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
)

exec sp_configure 'show advanced options',1
reconfigure

exec sp_configure 'xp_cmdshell',1
reconfigure

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 @cmdSQL
EXEC MASTER.DBO.XP_CMDSHELL @cmdSQL

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

执行看看！~这样备份就比较快了！~（同样试试其他数据库的 完整备份+差异备份+日志备份）

SQLServer 多点及时备份技巧

标签：