SQL server实现异地增量备份和全量备份的几种方法实现
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、目标服务器（备份存储服务器）配置</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 创建本地文件夹</a></li><li><a href="#_lab2_0_1">2. 配置共享权限</a></li><li><a href="#_lab2_0_2">3. 测试共享访问</a></li></ul><li><a href="#_label1">二、SQL Server服务器配置</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">1. 确认SQL Server服务账户</a></li><li><a href="#_lab2_1_4">2. 授予服务账户访问权限（可选）</a></li></ul><li><a href="#_label2">三、创建备份作业</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_5">1. 启用SQL Server代理</a></li><li><a href="#_lab2_2_6">2. 创建新作业</a></li><li><a href="#_lab2_2_7">3. 添加作业步骤</a></li><li><a href="#_lab2_2_8">4. 配置作业调度</a></li><li><a href="#_lab2_2_9">5. 测试作业</a></li></ul><li><a href="#_label3">四、常见问题解决</a></li><ul class="second_class_ul"></ul></ul></div><p>要将SQL Server数据库通过作业备份到同一局域网的另一台服务器，需要完成共享目录配置、权限设置和作业创建三个核心步骤。以下是详细操作指南：</p><p class="maodian"><a name="_label0"></a></p><h2>一、目标服务器（备份存储服务器）配置</h2>
<p>在局域网内的目标服务器（如<code>172.70.74.211</code>）上创建共享目录，用于存放备份文件。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 创建本地文件夹</h3>
<ul><li>在目标服务器上新建文件夹（例如 <code>D:\SQLBackups</code>），用于实际存储备份文件。</li></ul>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 配置共享权限</h3>
<ul><li>右键文件夹 → <strong>属性</strong> → <strong>共享</strong>→<strong>高级共享</strong>→ 勾选“共享此文件夹”。</li><li>共享名设为 <code>SQLBackups</code>（后续访问路径为 <code>\\172.70.74.211\SQLBackups</code>）。</li><li>点击<strong>权限</strong>→ 添加 <code>Everyone</code> 或指定用户（如 <code>Administrator</code>），并授予“读取”和“写入”权限。</li><li>切换到<strong>安全</strong>标签页 → 确保相同用户有“完全控制”权限（避免NTFS权限与共享权限冲突）。</li></ul>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 测试共享访问</h3>
<ul><li>在SQL Server所在服务器的“运行”中输入 <code>\\172.70.74.211\SQLBackups</code>，验证能否正常访问（无需输入密码或使用目标服务器账号登录）。</li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>二、SQL Server服务器配置</h2>
<p>确保SQL Server服务账户有权限访问目标服务器的共享目录。</p>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>1. 确认SQL Server服务账户</h3>
<ul><li>打开“服务” → 找到 <code>SQL Server (MSSQLSERVER)</code> → 查看“登录身份”（通常是 <code>NT Service\MSSQLSERVER</code> 或域账户）。</li></ul>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2. 授予服务账户访问权限（可选）</h3>
<ul><li>如果服务账户是本地账户（如 <code>NT Service\MSSQLSERVER</code>），需在目标服务器的共享目录权限中添加SQL Server服务器的计算机账户（格式：<code>域\SQL服务器名$</code>，例如 <code>WORKGROUP\SQLSERVER$</code>），并授予读写权限。</li><li>如果是域账户，直接在目标服务器共享权限中添加该域账户即可。</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>三、创建备份作业</h2>
<p>通过SQL Server代理创建定时备份作业，自动将数据库备份到目标服务器的共享目录。</p>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>1. 启用SQL Server代理</h3>
<ul><li>打开SQL Server Management Studio (SSMS) → 连接到数据库引擎 → 确保“SQL Server代理”已启动（右键→“启动”）。</li></ul>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>2. 创建新作业</h3>
<ul><li>展开“SQL Server代理” → 右键“作业” →<strong>新建作业</strong>。
<ul><li><strong>名称</strong>：<code>数据库异地备份</code>。</li><li><strong>所有者</strong>：保持默认（<code>sa</code>）。</li><li><strong>类别</strong>：选择“数据库维护”。</li></ul></li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091211180042.png" /></p>
<p class="maodian"><a name="_lab2_2_7"></a></p><h3>3. 添加作业步骤</h3>
<ul><li><p>切换到<strong>步骤</strong>→<strong>新建</strong>：</p>
<ul><li><strong>步骤名称</strong>：<code>执行备份</code>。</li><li><strong>类型</strong>：<code>Transact-SQL (T-SQL)</code>。</li><li><strong>数据库</strong>：选择要备份的数据库（如 <code>AIS20250224105414</code>）。</li><li><strong>命令</strong>：输入以下T-SQL脚本（替换为实际路径和数据库名）：<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091211180017.png" /></p></li></ul></li><li><p><strong>步骤1</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 步骤1：建立Administrator网络连接
DECLARE
@SharePath NVARCHAR(100),
@User NVARCHAR(100),
@Pwd NVARCHAR(50),
@Cmd NVARCHAR(4000)
-- 定义变量值（单独赋值，避免复杂拼接）
SET @SharePath = '\\172.70.74.211\SQLBackups'
SET @User = '172.70.74.211\Administrator'
SET @Pwd = '123456' -- 替换为实际密码
-- 创建临时表存储命令结果（用#临时表替代@表变量，避免作用域问题）
CREATE TABLE #Result (OutputText NVARCHAR(4000))
-- 1. 断开旧连接
SET @Cmd = 'net use "' + @SharePath + '" /delete /y'
DELETE FROM #Result
INSERT INTO #Result EXEC master.dbo.xp_cmdshell @Cmd
-- 2. 建立新连接（用双引号包裹路径和参数，兼容特殊字符）
SET @Cmd = 'net use "' + @SharePath + '" /user:' + @User + ' "' + @Pwd + '"'
DELETE FROM #Result
INSERT INTO #Result EXEC master.dbo.xp_cmdshell @Cmd
-- 3. 输出连接命令执行结果（用于排查错误）
PRINT '=== 网络连接命令执行结果 ==='
SELECT OutputText AS 执行结果 FROM #Result WHERE OutputText IS NOT NULL
-- 4. 验证共享目录是否可访问
SET @Cmd = 'dir "' + @SharePath + '"'
DELETE FROM #Result
INSERT INTO #Result EXEC master.dbo.xp_cmdshell @Cmd
-- 5. 判断连接状态
IF EXISTS (SELECT 1 FROM #Result WHERE OutputText LIKE '%<DIR>%')
BEGIN
PRINT '=== 连接成功 ==='
PRINT '已成功访问共享目录：' + @SharePath
END
ELSE
BEGIN
PRINT '=== 连接失败 ==='
RAISERROR('无法访问共享目录，请检查共享名、账号密码或权限', 16, 1)
RETURN
END
-- 删除临时表
DROP TABLE #Result
</pre></div></li><li><p><strong>步骤2</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 步骤2：执行备份
DECLARE @BackupType VARCHAR(10),
@BackupPath NVARCHAR(255),
@BackupName NVARCHAR(255),
@WeekDay INT;
-- 获取当前星期几（1=周一，7=周日）
SET @WeekDay = DATEPART(WEEKDAY, GETDATE());
-- 判定备份类型
IF @WeekDay = 7 OR NOT EXISTS (
-- 检查是否存在全量备份（首次执行时无全量，强制全量）
SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = 'AIS20250224105414'
AND type = 'D' -- 'D'表示全量备份
)
BEGIN
SET @BackupType = 'Full';
SET @BackupName = N'ERP全量备份';
END
ELSE
BEGIN
SET @BackupType = 'Diff';
SET @BackupName = N'ERP增量备份';
END
-- 构建备份路径
SET @BackupPath = N'\\172.70.74.211\SQLBackups\AIS20250224105414_'
+ @BackupType + '_'
+ CONVERT(VARCHAR(8), GETDATE(), 112)
+ '.bak';
-- 执行对应类型的备份
IF @BackupType = 'Full'
BEGIN
BACKUP DATABASE
TO DISK = @BackupPath
WITH INIT, -- 全量备份覆盖同名文件
NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD,
STATS = 10;
END
ELSE
BEGIN
BACKUP DATABASE
TO DISK = @BackupPath
WITH DIFFERENTIAL, -- 增量备份关键参数
NOINIT, -- 增量备份不覆盖，追加到备份集
NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD,
STATS = 10;
END
PRINT '备份完成！类型：' + @BackupType + '，路径：' + @BackupPath;
</pre></div></li><li><p><strong>步骤3</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 步骤3：断开网络连接
DECLARE
@SharePath NVARCHAR(100),
@Cmd NVARCHAR(4000)
-- 设置共享路径
SET @SharePath = '\\172.70.74.211\SQLBackups'
-- 构建完整命令（用双引号包裹路径，避免特殊字符问题）
SET @Cmd = 'net use "' + @SharePath + '" /delete /y'
-- 执行断开连接命令
EXEC master.dbo.xp_cmdshell @Cmd
-- 输出结果
PRINT '网络连接已断开：' + @SharePath
</pre></div></li></ul>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>4. 配置作业调度</h3>
<p>切换到<strong>调度</strong>→<strong>新建</strong>：</p>
<ul><li><strong>名称</strong>：<code>每日备份</code>。</li><li><strong>调度类型</strong>：<code>重复执行</code>。</li><li><strong>频率</strong>：例如“每天”、“凌晨2点”。</li><li>点击“确定”保存调度。</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091211180078.png" /></p>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>5. 测试作业</h3>
<p>右键新建的作业 →<strong>执行步骤</strong>→ 选择“执行备份” → 检查目标服务器共享目录是否生成备份文件。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091211180034.png" /></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202509/2025091211180022.png" /></p>
<p class="maodian"><a name="_label3"></a></p><h2>四、常见问题解决</h2>
<ol><li><p><strong>“无法访问网络路径”错误</strong></p>
<ul><li>检查共享目录路径是否正确（如 <code>\\172.70.74.211\SQLBackups</code>）。</li><li>验证SQL Server服务账户是否有访问权限（参考步骤二）。</li><li>关闭目标服务器防火墙或添加文件共享例外（端口139、445）。</li></ul></li><li><p><strong>备份文件为空或大小异常</strong></p>
<ul><li>检查T-SQL脚本中的 <code>BACKUP DATABASE</code> 语句是否正确。</li><li>确认数据库处于正常状态（非离线或恢复中）。</li></ul></li><li><p><strong>作业执行失败无日志</strong></p>
<ul><li>在作业属性的<strong>通知</strong>中，勾选“当作业失败时写入Windows事件日志”，通过“事件查看器”排查详细错误。</li></ul></li></ol>
<p>通过以上步骤，即可实现SQL Server数据库自动备份到局域网内的另一台服务器，确保数据安全和异地存储。</p>
頁:
[1]