通过数据库链接将SQL Server生产库备份到开发服务器的问题咨询

核心错误原因 #

• BACKUP DATABASE命令不支持直接指定链接服务器作为备份源，必须在生产SQL Server实例的上下文下执行该命令
• 原代码中调用sp_executesql的链接服务器指向错误，且动态SQL长度设置过小会被截断，路径转义也存在问题

前置配置 #

1. 开发侧准备共享目录：不要使用默认的管理员共享D$，专门将D:\Backup\blog路径设置为普通共享，例如共享名设为Dev_BlogBackup_Share
2. 权限配置：给生产SQL Server实例的服务账号授予该共享的「修改」共享权限，同时授予对应本地文件夹的「修改」NTFS权限
3. 确认开发侧使用的链接服务器PROD_DATABASE_LINK具备生产实例的ALTER ANY DATABASE权限（备份操作需要该权限）

正确的备份执行代码（在开发实例执行） #

-- 开发实例执行，通过链接服务器调用生产实例执行备份
DECLARE @SQL NVARCHAR(MAX)
DECLARE @BackupPath NVARCHAR(200) = N'\\开发服务器的IP或主机名\Dev_BlogBackup_Share\blog_FullBackup_' + CONVERT(VARCHAR(8),GETDATE(),112) + '.bak'

SET @SQL = N'
BACKUP DATABASE [blog]
TO DISK = N''' + @BackupPath + '''
WITH NOFORMAT, NOINIT,
NAME = N''blog-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
'

-- 注意这里调用的是指向生产实例的链接服务器的sp_executesql
EXEC [PROD_DATABASE_LINK].master.dbo.sp_executesql @SQL

后续自动恢复配置（备份完成后在开发实例直接执行） #

RESTORE DATABASE [blog]
FROM DISK = N'D:\Backup\blog\blog_FullBackup_' + CONVERT(VARCHAR(8),GETDATE(),112) + '.bak'
WITH 
  REPLACE, -- 覆盖现有开发库
  MOVE N'blog' TO N'你开发库的数据文件路径\blog.mdf', -- 替换为实际路径
  MOVE N'blog_log' TO N'你开发库的日志文件路径\blog_log.ldf', -- 替换为实际路径
  STATS = 10,
  RECOVERY

定时任务配置 #

将上述两段代码放在同一个SQL Server代理作业中，部署在开发实例上，设置每日夜间非业务时段执行即可实现自动同步生产数据到开发环境。

注意事项 #

• 如果执行时出现生产实例无法访问开发共享路径的报错，需检查生产SQL Server服务账号权限，以及Kerberos SPN配置（解决Windows认证跨服务器访问的双跳问题）
• 可根据需要添加备份压缩参数COMPRESSION减少备份文件体积，提升传输速度

内容的提问来源于stack exchange，提问作者pmcs