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"
}
执行 SQL 备份
Invoke-Sqlcmd -ServerInstance "localhost" -Query $backupCommand
传输到异地
Copy-Item "$localBackupPath$fileName" -Destination $remoteBackupPath -Force
清理旧备份（保留30天）
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;
设置备份加密（SQL Server 2014+）：
-- 创建主密钥
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 云存储
- **恢复复杂性**：不同的恢复流程
- **合规要求**：数据保留和加密要求
