SQL server实现异地增量备份和全量备份的几种方法实现

1. 本地备份 + 文件传输（基础方案）

全量备份

-- 创建完整备份
BACKUP DATABASE [YourDatabase] 
TO DISK = 'D:\Backups\FullBackup.bak'
WITH INIT, COMPRESSION;

-- 每日完整备份脚本
DECLARE @backupPath NVARCHAR(500)
DECLARE @backupName NVARCHAR(500)
SET @backupPath = 'D:\Backups\'
SET @backupName = @backupPath + 'FullBackup_' + 
                  REPLACE(CONVERT(VARCHAR, GETDATE(), 112), '-', '') + 
                  '_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', ''), ' ', '') + '.bak'

BACKUP DATABASE [YourDatabase] 
TO DISK = @backupName
WITH COMPRESSION, STATS = 10;

差异备份（增量）

-- 创建差异备份
BACKUP DATABASE [YourDatabase] 
TO DISK = 'D:\Backups\DiffBackup.bak'
WITH DIFFERENTIAL, COMPRESSION;

文件传输到异地

# PowerShell 脚本传输备份文件
$source = "D:\Backups\"
$destination = "\\异地服务器\BackupShare\"

# 使用 Robocopy（支持断点续传）
robocopy $source $destination /MIR /Z /W:5 /R:3 /NP

# 或使用 SMB 共享复制
Copy-Item -Path $source\* -Destination $destination -Force

2. 备份到 URL（Azure Blob Storage）

配置备份到 Azure

-- 1. 创建凭据
CREATE CREDENTIAL [https://youraccount.blob.core.windows.net/backups] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '你的SAS令牌';

-- 2. 完整备份到 Azure
BACKUP DATABASE [YourDatabase] 
TO URL = 'https://youraccount.blob.core.windows.net/backups/FullBackup.bak'
WITH COMPRESSION, FORMAT, STATS = 10;

-- 3. 差异备份到 Azure
BACKUP DATABASE [YourDatabase] 
TO URL = 'https://youraccount.blob.core.windows.net/backups/DiffBackup.bak'
WITH DIFFERENTIAL, COMPRESSION;

自动备份策略

-- 创建备份作业（完整+差异组合）
DECLARE @backupURL NVARCHAR(500)
DECLARE @backupFileName NVARCHAR(100)

-- 完整备份（每周日）
IF DATEPART(WEEKDAY, GETDATE()) = 1  -- 周日
BEGIN
    SET @backupFileName = 'Full_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 112), '-', '') + '.bak'
    SET @backupURL = 'https://youraccount.blob.core.windows.net/backups/' + @backupFileName

    BACKUP DATABASE [YourDatabase] 
    TO URL = @backupURL
    WITH COMPRESSION, FORMAT;
END
-- 差异备份（周一到周六）
ELSE
BEGIN
    SET @backupFileName = 'Diff_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 112), '-', '') + '.bak'
    SET @backupURL = 'https://youraccount.blob.core.windows.net/backups/' + @backupFileName

    BACKUP DATABASE [YourDatabase] 
    TO URL = @backupURL
    WITH DIFFERENTIAL, COMPRESSION;
END

3. 日志传送（Log Shipping）

配置日志传送

-- 主服务器配置
USE [master]
GO

-- 启用完整恢复模式
ALTER DATABASE [YourDatabase] 
SET RECOVERY FULL;

-- 完整备份
BACKUP DATABASE [YourDatabase] 
TO DISK = 'D:\Backups\FullForLogShipping.bak';

-- 配置日志传送
EXEC sp_add_log_shipping_primary_database 
    @database = N'YourDatabase',
    @backup_directory = N'D:\Backups\',
    @backup_share = N'\\主服务器\BackupShare\',
    @backup_job_name = N'LSBackup_YourDatabase';

-- 添加辅助服务器
EXEC sp_add_log_shipping_secondary_primary
    @primary_server = N'主服务器',
    @primary_database = N'YourDatabase',
    @backup_source_directory = N'\\主服务器\BackupShare\',
    @backup_destination_directory = N'\\异地服务器\BackupShare\',
    @copy_job_name = N'LSCopy_YourDatabase',
    @restore_job_name = N'LSRestore_YourDatabase',
    @secondary_server = N'异地服务器',
    @secondary_database = N'YourDatabase';

4. Always On 可用性组

配置 Always On

-- 主副本创建端点
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = ALL);

-- 创建可用性组
CREATE AVAILABILITY GROUP [AG_YourDatabase]
WITH (AUTOMATED_BACKUP_PREFERENCES = SECONDARY)
FOR DATABASE [YourDatabase]
REPLICA ON 
    N'主服务器' WITH (ENDPOINT_URL = N'TCP://主服务器:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50),
    N'异地服务器' WITH (ENDPOINT_URL = N'TCP://异地服务器:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50);

5. 维护计划 + PowerShell

SQL Server 维护计划

创建维护计划：

• 完整备份作业（每周）
• 差异备份作业（每天）
• 事务日志备份作业（每小时）

使用 PowerShell 传输：

# 备份传输脚本
param(
 [string]$DatabaseName,
 [string]$BackupType
)

$timestamp = Get-Date -Format "yyyyMMdd_HHmmss" $localBackupPath = "D:\Backups\" $remoteBackupPath = "\异地服务器\BackupShare\"

if ($BackupType -eq "Full") { $fileName = "$DatabaseNameFull$timestamp.bak" $backupCommand = "BACKUP DATABASE [$DatabaseName] TO DISK='$localBackupPath$fileName' WITH COMPRESSION" } elseif ($BackupType -eq "Diff") { $fileName = "$DatabaseNameDiff$timestamp.bak" $backupCommand = "BACKUP DATABASE [$DatabaseName] TO DISK='$localBackupPath$fileName' WITH DIFFERENTIAL, COMPRESSION" }

Invoke-Sqlcmd -ServerInstance "localhost" -Query $backupCommand

Copy-Item "$localBackupPath$fileName" -Destination $remoteBackupPath -Force

Get-ChildItem -Path $localBackupPath -Filter "*.bak" | Where-Object {$_.LastWriteTime -lt (Get-Date).AddDays(-30)} | Remove-Item -Force

## 6. **第三方工具方案**

### Redgate SQL Backup
```sql
-- Redgate SQL Backup 示例
EXECUTE master..sqlbackup 
'-SQL "BACKUP DATABASE [YourDatabase] 
TO DISK = ''\\异地服务器\Backups\YourDatabase.sqb'' 
WITH DIFFERENTIAL, COMPRESSION = 4, THREADCOUNT = 4"'

备份策略建议

推荐方案：完整+差异备份

-- 完整备份策略（每周日）
BACKUP DATABASE [YourDatabase] 
TO DISK = 'D:\Backups\Full_Weekly.bak'
WITH COMPRESSION, FORMAT;

-- 差异备份策略（每天）
BACKUP DATABASE [YourDatabase] 
TO DISK = 'D:\Backups\Diff_Daily.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- 事务日志备份（每15分钟，如果需要时点恢复）
BACKUP LOG [YourDatabase] 
TO DISK = 'D:\LogBackups\Log.trn'
WITH COMPRESSION;

自动化调度建议

SQL Server 代理作业：

• 完整备份：每周日凌晨2点
• 差异备份：每天凌晨1点（除周日）
• 日志备份：每15分钟

文件清理策略：

-- 清理旧备份文件
EXEC sp_delete_backuphistory @oldest_date = '2024-01-01';

-- 或使用维护计划清理任务

最佳实践

-- 定期验证备份
RESTORE VERIFYONLY 
FROM DISK = 'D:\Backups\FullBackup.bak';

-- 实际恢复测试（定期进行） RESTORE DATABASE [TestDB] FROM DISK = 'D:\Backups\FullBackup.bak' WITH NORECOVERY, REPLACE;

2. **监控备份状态**：
```sql
-- 查看备份历史
SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    backup_size/1024/1024 as BackupSizeMB,
    type
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC;

-- 创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '强密码';

-- 创建证书 CREATE CERTIFICATE BackupCertificate WITH SUBJECT = 'Backup Encryption Certificate';

-- 加密备份 BACKUP DATABASE [YourDatabase] TO DISK = 'D:\Backups\EncryptedBackup.bak' WITH COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCertificate);

选择方案时考虑因素：
- **RTO/RPO要求**：恢复时间目标/恢复点目标
- **网络带宽**：影响传输速度和成本
- **存储成本**：本地存储 vs 云存储
- **恢复复杂性**：不同的恢复流程
- **合规要求**：数据保留和加密要求