SQLServer 备份恢复命令

备份命令

完整备份

生产场景示例：每周日晚上执行数据库完整备份，作为基础备份，用于灾难恢复和日常恢复。

sql

-- 备份数据库到指定位置
BACKUP DATABASE DatabaseName TO DISK = 'D:\Backup\DatabaseName_20231227.bak';

-- 备份数据库到多个文件（并行备份，提高速度）
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_1.bak',
    DISK = 'D:\Backup\DatabaseName_2.bak',
    DISK = 'D:\Backup\DatabaseName_3.bak';

-- 带压缩的备份（SQL Server 2008R2+）
BACKUP DATABASE DatabaseName TO DISK = 'D:\Backup\DatabaseName.bak' WITH COMPRESSION;

-- 带描述和检查选项的备份
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName.bak'
WITH 
    DESCRIPTION = '完整备份 - 2023-12-27',
    CHECKSUM,  -- 生成校验和
    VERIFY; -- 备份后验证

-- 覆盖现有备份文件（默认行为）
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName.bak'
WITH INIT;

差异备份

生产场景示例：每天中午和晚上执行差异备份，减少备份时间和存储空间，用于快速恢复到最近的差异点。

sql

-- 差异备份到指定位置
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Diff_20231227.bak'
WITH DIFFERENTIAL;

-- 带压缩的差异备份
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

事务日志备份

生产场景示例：每15分钟执行事务日志备份，实现时间点恢复，用于恢复到特定时间点或防止数据丢失。

sql

-- 事务日志备份
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Log_20231227_1430.trn';

-- 带压缩的事务日志备份
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Log.bak'
WITH COMPRESSION;

-- 尾日志备份（用于故障恢复，备份未提交的事务）
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_TailLog.trn'
WITH NORECOVERY; -- 备份后将数据库置于恢复状态

-- 尾日志备份（包含未提交事务，即使数据库不可访问）
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_TailLog.trn'
WITH NO_TRUNCATE;

文件和文件组备份

生产场景示例：对于大型数据库，按文件组进行备份，减少单次备份时间，便于针对性恢复。

sql

-- 备份特定文件组
BACKUP DATABASE DatabaseName FILEGROUP = 'PRIMARY'
TO DISK = 'D:\Backup\DatabaseName_PrimaryFG.bak';

-- 备份特定数据文件
BACKUP DATABASE DatabaseName FILE = 'DatabaseName_Data'
TO DISK = 'D:\Backup\DatabaseName_DataFile.bak';

-- 文件组差异备份
BACKUP DATABASE DatabaseName FILEGROUP = 'PRIMARY'
TO DISK = 'D:\Backup\DatabaseName_PrimaryFG_Diff.bak'
WITH DIFFERENTIAL;

部分备份

生产场景示例：对于只读文件组较多的数据库，只备份读写文件组，减少备份时间和存储空间。

sql

-- 部分备份（仅备份读/写文件组）
BACKUP DATABASE DatabaseName READ_WRITE_FILEGROUPS
TO DISK = 'D:\Backup\DatabaseName_Partial.bak';

-- 部分差异备份
BACKUP DATABASE DatabaseName READ_WRITE_FILEGROUPS
TO DISK = 'D:\Backup\DatabaseName_Partial_Diff.bak'
WITH DIFFERENTIAL;

恢复命令

完整恢复（简单恢复模式）

生产场景示例：在简单恢复模式下，从完整备份恢复数据库，用于测试环境恢复或简单数据库恢复。

sql

-- 恢复数据库（简单恢复模式）
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH REPLACE; -- 覆盖现有数据库

-- 恢复到新数据库
RESTORE DATABASE NewDatabaseName
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH 
    MOVE 'DatabaseName_Data' TO 'D:\MSSQL\Data\NewDatabaseName.mdf',
    MOVE 'DatabaseName_Log' TO 'D:\MSSQL\Log\NewDatabaseName.ldf',
    REPLACE;

完整恢复 + 事务日志恢复（完整恢复模式）

生产场景示例：在完整恢复模式下，从完整备份、差异备份和事务日志备份恢复数据库，实现完整恢复。

sql

-- 步骤 1：从完整备份恢复（NORECOVERY 保持数据库在恢复状态）
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Full_20231227.bak'
WITH NORECOVERY;

-- 步骤 2：从差异备份恢复（如果有）
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Diff_20231227.bak'
WITH NORECOVERY;

-- 步骤 3：从事务日志备份恢复（按顺序）
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log_20231227_1000.trn'
WITH NORECOVERY;

RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log_20231227_1100.trn'
WITH NORECOVERY;

RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log_20231227_1200.trn'
WITH RECOVERY; -- 最后一个日志恢复使用 RECOVERY

时间点恢复

生产场景示例：恢复数据库到特定时间点，用于恢复误删除或误修改的数据。

sql

-- 步骤 1：从完整备份恢复
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Full.bak'
WITH NORECOVERY;

-- 步骤 2：恢复差异备份（如果有）
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Diff.bak'
WITH NORECOVERY;

-- 步骤 3：恢复到特定时间点
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH 
    RECOVERY,
    STOPAT = '2023-12-27 11:30:00.000';

-- 恢复到特定标记
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH 
    RECOVERY,
    STOPATMARK = 'MyTransactionMark';

页级恢复

生产场景示例：恢复数据库中的损坏页，避免完整数据库恢复，减少停机时间。

sql

-- 页级恢复
RESTORE DATABASE DatabaseName
PAGE = '1:100', '2:200'
FROM DISK = 'D:\Backup\DatabaseName_Full.bak'
WITH NORECOVERY;

-- 恢复后续的事务日志
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH RECOVERY;

文件和文件组恢复

生产场景示例：恢复损坏的文件组，减少恢复时间，适用于大型数据库。

sql

-- 步骤 1：恢复文件组
RESTORE DATABASE DatabaseName FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\Backup\DatabaseName_PrimaryFG.bak'
WITH NORECOVERY;

-- 步骤 2：恢复文件组差异备份（如果有）
RESTORE DATABASE DatabaseName FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\Backup\DatabaseName_PrimaryFG_Diff.bak'
WITH NORECOVERY;

-- 步骤 3：恢复事务日志
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH RECOVERY;

部分恢复

生产场景示例：仅恢复读写文件组，用于快速恢复数据库的核心功能。

sql

-- 部分恢复
RESTORE DATABASE DatabaseName READ_WRITE_FILEGROUPS
FROM DISK = 'D:\Backup\DatabaseName_Partial.bak'
WITH NORECOVERY;

-- 恢复部分差异备份
RESTORE DATABASE DatabaseName READ_WRITE_FILEGROUPS
FROM DISK = 'D:\Backup\DatabaseName_Partial_Diff.bak'
WITH NORECOVERY;

-- 恢复事务日志
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH RECOVERY;

恢复到备用服务器

生产场景示例：用于日志传送或数据库镜像配置，准备备用数据库。

sql

-- 恢复到备用服务器（用于日志传送）
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Full.bak'
WITH 
    STANDBY = 'D:\MSSQL\Data\DatabaseName_Standby.bak',
    MOVE 'DatabaseName_Data' TO 'D:\MSSQL\Data\DatabaseName.mdf',
    MOVE 'DatabaseName_Log' TO 'D:\MSSQL\Log\DatabaseName.ldf';

-- 应用事务日志到备用数据库
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log.trn'
WITH STANDBY = 'D:\MSSQL\Data\DatabaseName_Standby.bak';

备份集和备份设备管理

查看备份设备

生产场景示例：管理SQL Server备份设备，用于集中管理备份。

sql

-- 查看所有备份设备
SELECT * FROM sys.backup_devices;

-- 创建备份设备
EXEC sp_addumpdevice 'disk', 'MyBackupDevice', 'D:\Backup\MyBackupDevice.bak';

-- 删除备份设备
EXEC sp_dropdevice 'MyBackupDevice', 'delfile';

查看备份集信息

生产场景示例：查看备份媒体中的备份集信息，用于验证备份和恢复规划。

sql

-- 查看备份媒体中的备份集
RESTORE HEADERONLY FROM DISK = 'D:\Backup\DatabaseName.bak';

-- 查看备份集中的文件列表
RESTORE FILELISTONLY FROM DISK = 'D:\Backup\DatabaseName.bak';

-- 查看备份集的校验和信息
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\DatabaseName.bak' WITH CHECKSUM;

-- 查看 msdb 中的备份历史
SELECT 
    b.database_name,
    b.backup_start_date,
    b.backup_finish_date,
    b.backup_size,
    m.physical_device_name,
    CASE b.type 
        WHEN 'D' THEN '完整备份' 
        WHEN 'I' THEN '差异备份' 
        WHEN 'L' THEN '事务日志备份' 
        WHEN 'F' THEN '文件或文件组备份' 
        WHEN 'G' THEN '差异文件备份' 
        WHEN 'P' THEN '部分备份' 
        WHEN 'Q' THEN '差异部分备份' 
        ELSE b.type 
    END AS backup_type
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE b.database_name = 'DatabaseName'
ORDER BY b.backup_start_date DESC;

备份恢复策略示例

完整备份 + 差异备份 + 事务日志备份策略

生产场景示例：企业级数据库常用备份策略，兼顾备份速度和恢复能力。

sql

-- 每周日 22:00 执行完整备份
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Full_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
WITH INIT, COMPRESSION, CHECKSUM, VERIFY;

-- 每天 12:00 和 18:00 执行差异备份
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Diff_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108), ':', '') + '.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- 每 15 分钟执行事务日志备份
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Log_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.trn'
WITH INIT, COMPRESSION, CHECKSUM;

灾难恢复场景恢复示例

生产场景示例：数据库损坏时的完整恢复流程，包括尾日志备份和完整恢复。

sql

-- 场景：数据库损坏，需要从备份恢复

-- 1. 备份尾日志（如果可能）
BACKUP LOG DatabaseName
TO DISK = 'D:\Backup\DatabaseName_TailLog.trn'
WITH NO_TRUNCATE, NORECOVERY;

-- 2. 恢复完整备份
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Full_20231224.bak'
WITH NORECOVERY, REPLACE;

-- 3. 恢复最新的差异备份
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Diff_20231227_1800.bak'
WITH NORECOVERY;

-- 4. 恢复所有事务日志（按顺序）
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log_20231227_1815.trn'
WITH NORECOVERY;

RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Log_20231227_1830.trn'
WITH NORECOVERY;

-- 5. 恢复尾日志备份
RESTORE LOG DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_TailLog.trn'
WITH RECOVERY;

数据库迁移场景恢复示例

生产场景示例：将数据库从旧服务器迁移到新服务器的完整流程。

sql

-- 场景：将数据库从旧服务器迁移到新服务器

-- 在旧服务器上备份数据库
BACKUP DATABASE DatabaseName
TO DISK = '\\OldServer\Backup\DatabaseName_Migration.bak'
WITH INIT, COMPRESSION, CHECKSUM, VERIFY;

-- 在新服务器上恢复数据库
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Migration.bak'
WITH 
    MOVE 'DatabaseName_Data' TO 'D:\MSSQL\Data\DatabaseName.mdf',
    MOVE 'DatabaseName_Log' TO 'D:\MSSQL\Log\DatabaseName.ldf',
    REPLACE, RECOVERY;

-- 更新统计信息
UPDATE STATISTICS DatabaseName WITH FULLSCAN;

-- 更新数据库兼容级别（如果需要）
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 150; -- SQL Server 2019

版本差异

SQL Server 2012及以前

备份压缩需要企业版
没有备份加密功能
不支持Azure Blob存储备份
不支持备份到URL

SQL Server 2014

备份压缩在所有版本可用
引入备份加密功能
支持Azure Blob存储备份
支持备份到URL

SQL Server 2016

增强了备份加密功能
引入了STANDBY_MIRROR选项
增强了备份到Azure Blob存储的功能

SQL Server 2017

支持备份到Azure Blob存储的增量备份
增强了备份压缩算法
引入了备份到Azure Blob存储的快照备份

SQL Server 2019

增强了智能备份压缩
引入了备份到Azure Blob存储的增量快照备份
支持备份到Azure Blob存储的分层存储

SQL Server 2022

增强了备份到Azure Blob存储的功能
引入了备份到Azure Blob存储的增量快照备份的改进
支持备份到Azure Blob存储的分层存储的改进

备份恢复最佳实践

备份最佳实践

使用压缩备份：减少备份大小和备份时间，节省存储成本
使用校验和：确保备份的完整性，检测备份过程中的错误
定期验证备份：确保备份可以恢复，避免备份损坏
使用多个备份设备：提高备份速度和可靠性，实现并行备份
存储备份到异地：防止本地灾难，实现灾难恢复
实施 3-2-1 备份策略：
- 3 份备份副本
- 2 种不同的存储介质
- 1 份异地备份
定期清理旧备份：避免存储空间不足，节省存储成本
使用事务日志备份：实现时间点恢复，减少数据丢失风险
备份系统数据库：master、model 和 msdb 数据库需要定期备份
使用备份加密：保护敏感数据，防止备份泄露

恢复最佳实践

测试恢复流程：定期进行恢复演练，确保恢复流程有效
记录恢复步骤：确保在紧急情况下能够快速恢复
使用NORECOVERY/RECOVERY选项：控制数据库恢复状态，实现多步骤恢复
验证恢复结果：恢复后检查数据库完整性，确保数据完整
更新统计信息：恢复后更新数据库统计信息，提高查询性能
测试应用程序连接：确保应用程序能够正常连接到恢复后的数据库
恢复后监控：恢复后监控数据库性能，确保恢复成功
使用时间点恢复：在可能的情况下，恢复到错误发生前的时间点
考虑页级恢复：对于大型数据库，使用页级恢复减少停机时间
制定恢复计划：针对不同的故障场景，制定详细的恢复计划

常用备份恢复脚本

自动备份脚本

生产场景示例：创建自动备份存储过程，用于定期备份数据库。

sql

-- 创建自动备份存储过程
CREATE PROCEDURE dbo.usp_BackupDatabase
    @DatabaseName SYSNAME = NULL,
    @BackupType VARCHAR(10) = 'FULL', -- FULL, DIFF, LOG
    @BackupPath VARCHAR(255) = 'D:\Backup\'
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 如果未指定数据库名，则备份所有用户数据库
    IF @DatabaseName IS NULL
    BEGIN
        DECLARE db_cursor CURSOR FOR 
        SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
        
        OPEN db_cursor;
        FETCH NEXT FROM db_cursor INTO @DatabaseName;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC dbo.usp_BackupDatabase @DatabaseName, @BackupType, @BackupPath;
            FETCH NEXT FROM db_cursor INTO @DatabaseName;
        END
        
        CLOSE db_cursor;
        DEALLOCATE db_cursor;
        RETURN;
    END
    
    -- 构建备份文件名
    DECLARE @FileName VARCHAR(255);
    DECLARE @FileExtension VARCHAR(4);
    
    SELECT @FileExtension = 
        CASE @BackupType
            WHEN 'FULL' THEN '.bak'
            WHEN 'DIFF' THEN '_Diff.bak'
            WHEN 'LOG' THEN '_Log.trn'
            ELSE '.bak'
        END;
    
    SET @FileName = @BackupPath + @DatabaseName + '_' + 
        CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + 
        REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + 
        @FileExtension;
    
    -- 执行备份
    BEGIN TRY
        IF @BackupType = 'FULL'
        BEGIN
            BACKUP DATABASE @DatabaseName 
            TO DISK = @FileName 
            WITH INIT, COMPRESSION, CHECKSUM, VERIFY;
        END
        ELSE IF @BackupType = 'DIFF'
        BEGIN
            BACKUP DATABASE @DatabaseName 
            TO DISK = @FileName 
            WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
        END
        ELSE IF @BackupType = 'LOG'
        BEGIN
            BACKUP LOG @DatabaseName 
            TO DISK = @FileName 
            WITH INIT, COMPRESSION, CHECKSUM;
        END
        
        -- 记录备份成功
        INSERT INTO dbo.BackupLog (DatabaseName, BackupType, BackupFileName, BackupDate, Status)
        VALUES (@DatabaseName, @BackupType, @FileName, GETDATE(), 'SUCCESS');
        
    END TRY
    BEGIN CATCH
        -- 记录备份失败
        INSERT INTO dbo.BackupLog (DatabaseName, BackupType, BackupFileName, BackupDate, Status, ErrorMessage)
        VALUES (@DatabaseName, @BackupType, @FileName, GETDATE(), 'FAILED', ERROR_MESSAGE());
    END CATCH
END;
GO

-- 创建备份日志表
CREATE TABLE dbo.BackupLog
(
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    DatabaseName SYSNAME NOT NULL,
    BackupType VARCHAR(10) NOT NULL,
    BackupFileName VARCHAR(255) NOT NULL,
    BackupDate DATETIME NOT NULL,
    Status VARCHAR(10) NOT NULL, -- SUCCESS, FAILED
    ErrorMessage VARCHAR(MAX) NULL
);
GO

备份验证脚本

生产场景示例：创建备份验证存储过程，用于定期验证备份文件的完整性。

sql

-- 验证备份文件
CREATE PROCEDURE dbo.usp_VerifyBackup
    @BackupFilePath VARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Result INT;
    DECLARE @Message VARCHAR(MAX);
    
    BEGIN TRY
        -- 验证备份文件
        RESTORE VERIFYONLY FROM DISK = @BackupFilePath WITH CHECKSUM;
        
        SET @Result = 0;
        SET @Message = '备份文件验证成功: ' + @BackupFilePath;
        
    END TRY
    BEGIN CATCH
        SET @Result = 1;
        SET @Message = '备份文件验证失败: ' + @BackupFilePath + CHAR(13) + ERROR_MESSAGE();
    END CATCH
    
    -- 记录验证结果
    INSERT INTO dbo.BackupVerificationLog (BackupFilePath, VerificationDate, Result, Message)
    VALUES (@BackupFilePath, GETDATE(), @Result, @Message);
    
    -- 返回结果
    SELECT @Result AS Result, @Message AS Message;
END;
GO

-- 创建备份验证日志表
CREATE TABLE dbo.BackupVerificationLog
(
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    BackupFilePath VARCHAR(255) NOT NULL,
    VerificationDate DATETIME NOT NULL,
    Result INT NOT NULL, -- 0 = 成功, 1 = 失败
    Message VARCHAR(MAX) NOT NULL
);
GO

恢复演练脚本

生产场景示例：创建恢复演练存储过程，用于定期测试恢复流程。

sql

-- 恢复演练存储过程
CREATE PROCEDURE dbo.usp_RecoveryDrill
    @DatabaseName SYSNAME,
    @FullBackupPath VARCHAR(255),
    @DiffBackupPath VARCHAR(255) = NULL,
    @LogBackupPath VARCHAR(255) = NULL,
    @TestDatabaseName SYSNAME = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 如果未指定测试数据库名，则使用原数据库名加上_Test后缀
    IF @TestDatabaseName IS NULL
        SET @TestDatabaseName = @DatabaseName + '_Test';
    
    -- 构建数据文件和日志文件路径
    DECLARE @DataFilePath VARCHAR(255);
    DECLARE @LogFilePath VARCHAR(255);
    
    SELECT 
        @DataFilePath = REPLACE(physical_name, '.mdf', '_Test.mdf'),
        @LogFilePath = REPLACE(physical_name, '.ldf', '_Test.ldf')
    FROM sys.master_files
    WHERE database_id = DB_ID(@DatabaseName) AND type = 0; -- 0 = 数据文件
    
    BEGIN TRY
        -- 1. 恢复完整备份
        RESTORE DATABASE @TestDatabaseName
        FROM DISK = @FullBackupPath
        WITH 
            MOVE @DatabaseName + '_Data' TO @DataFilePath,
            MOVE @DatabaseName + '_Log' TO @LogFilePath,
            NORECOVERY, REPLACE;
        
        -- 2. 恢复差异备份（如果提供）
        IF @DiffBackupPath IS NOT NULL AND @DiffBackupPath != ''
        BEGIN
            RESTORE DATABASE @TestDatabaseName
            FROM DISK = @DiffBackupPath
            WITH NORECOVERY;
        END
        
        -- 3. 恢复事务日志备份（如果提供）
        IF @LogBackupPath IS NOT NULL AND @LogBackupPath != ''
        BEGIN
            RESTORE LOG @TestDatabaseName
            FROM DISK = @LogBackupPath
            WITH RECOVERY;
        END
        ELSE
        BEGIN
            -- 如果没有日志备份，则恢复数据库
            RESTORE DATABASE @TestDatabaseName WITH RECOVERY;
        END
        
        -- 4. 验证数据库完整性
        DBCC CHECKDB(@TestDatabaseName) WITH NO_INFOMSGS;
        
        -- 5. 更新统计信息
        EXEC sp_updatestats @resample = 'RESAMPLE';
        
        -- 6. 记录恢复演练结果
        INSERT INTO dbo.RecoveryDrillLog (DatabaseName, TestDatabaseName, FullBackupPath, DiffBackupPath, LogBackupPath, DrillDate, Status)
        VALUES (@DatabaseName, @TestDatabaseName, @FullBackupPath, @DiffBackupPath, @LogBackupPath, GETDATE(), 'SUCCESS');
        
        -- 7. 返回成功消息
        SELECT '恢复演练成功' AS Result, @TestDatabaseName AS TestDatabaseName;
        
    END TRY
    BEGIN CATCH
        -- 记录恢复演练失败
        INSERT INTO dbo.RecoveryDrillLog (DatabaseName, TestDatabaseName, FullBackupPath, DiffBackupPath, LogBackupPath, DrillDate, Status, ErrorMessage)
        VALUES (@DatabaseName, @TestDatabaseName, @FullBackupPath, @DiffBackupPath, @LogBackupPath, GETDATE(), 'FAILED', ERROR_MESSAGE());
        
        -- 尝试回滚（如果需要）
        IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TestDatabaseName)
        BEGIN
            ALTER DATABASE @TestDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            DROP DATABASE @TestDatabaseName;
        END
        
        -- 返回失败消息
        SELECT '恢复演练失败' AS Result, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END;
GO

-- 创建恢复演练日志表
CREATE TABLE dbo.RecoveryDrillLog
(
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    DatabaseName SYSNAME NOT NULL,
    TestDatabaseName SYSNAME NOT NULL,
    FullBackupPath VARCHAR(255) NOT NULL,
    DiffBackupPath VARCHAR(255) NULL,
    LogBackupPath VARCHAR(255) NULL,
    DrillDate DATETIME NOT NULL,
    Status VARCHAR(10) NOT NULL, -- SUCCESS, FAILED
    ErrorMessage VARCHAR(MAX) NULL
);
GO

常见问题（FAQ）

如何确定备份文件的完整性？

解答：

sql

-- 使用 RESTORE VERIFYONLY 验证备份文件
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\DatabaseName.bak' WITH CHECKSUM;

-- 使用 DBCC CHECKDB 验证恢复后的数据库完整性
RESTORE DATABASE TestDB FROM DISK = 'D:\Backup\DatabaseName.bak' WITH NORECOVERY;
RESTORE DATABASE TestDB WITH RECOVERY;
DBCC CHECKDB('TestDB') WITH NO_INFOMSGS;
DROP DATABASE TestDB;

如何处理备份失败的情况？

解答：

查看错误日志：EXEC xp_readerrorlog
检查磁盘空间：确保备份目标有足够的空间
检查权限：确保 SQL Server 服务账户有足够的权限访问备份目标
检查数据库状态：确保数据库处于在线状态
检查备份设备：确保备份设备可用
使用 WITH CHECKSUM 选项：检测备份过程中的错误
尝试使用不同的备份类型：如果完整备份失败，尝试差异备份或日志备份
检查备份介质：如果使用磁带或其他物理介质，检查介质是否损坏

如何实现自动备份清理？

解答：

powershell

# PowerShell 脚本：清理指定天数前的备份文件
$BackupPath = "D:\Backup\"
$RetentionDays = 7

# 获取当前日期
$CurrentDate = Get-Date

# 计算保留期限
$RetentionDate = $CurrentDate.AddDays(-$RetentionDays)

# 获取并删除旧备份文件
Get-ChildItem -Path $BackupPath -Include *.bak, *.trn -Recurse | 
Where-Object { $_.LastWriteTime -lt $RetentionDate } | 
Remove-Item -Force

如何备份系统数据库？

解答：

sql

-- 备份 master 数据库
BACKUP DATABASE master TO DISK = 'D:\Backup\master.bak' WITH COMPRESSION, CHECKSUM;

-- 备份 model 数据库
BACKUP DATABASE model TO DISK = 'D:\Backup\model.bak' WITH COMPRESSION, CHECKSUM;

-- 备份 msdb 数据库
BACKUP DATABASE msdb TO DISK = 'D:\Backup\msdb.bak' WITH COMPRESSION, CHECKSUM;

如何从损坏的备份文件中恢复数据？

解答：

sql

-- 使用 CONTINUE_AFTER_ERROR 选项尝试恢复
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH CONTINUE_AFTER_ERROR, REPLACE;

-- 如果是事务日志备份损坏，尝试使用 NO_TRUNCATE 选项备份尾日志
BACKUP LOG DatabaseName TO DISK = 'D:\Backup\TailLog.trn' WITH NO_TRUNCATE;

-- 尝试使用 PAGE 选项恢复特定页
RESTORE DATABASE DatabaseName PAGE = '1:100' FROM DISK = 'D:\Backup\DatabaseName.bak' WITH NORECOVERY;
RESTORE LOG DatabaseName FROM DISK = 'D:\Backup\DatabaseName_Log.trn' WITH RECOVERY;

如何监控备份和恢复进度？

解答：

sql

-- 查看当前正在执行的备份和恢复操作
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.total_elapsed_time,
    t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG');

-- 查看备份和恢复历史记录
SELECT 
    b.database_name,
    b.command,
    b.percent_complete,
    b.start_time,
    b.end_time,
    b.total_elapsed_time,
    m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
ORDER BY b.start_time DESC;

如何优化备份和恢复性能？

解答：

使用多个备份设备进行并行备份
使用压缩备份减少备份大小和备份时间
使用固态驱动器（SSD）存储备份文件
调整备份缓冲区大小：BACKUP DATABASE DatabaseName TO DISK = 'D:\Backup\DatabaseName.bak' WITH BUFFERCOUNT = 64, BLOCKSIZE = 65536;
在业务低峰期执行备份操作
使用增量备份策略减少备份时间
调整 SQL Server 配置：增加 max degree of parallelism 或 backup compression default
使用备份加速技术：如 SQL Server 2019+ 的智能备份压缩

如何实现跨服务器备份？

解答：

sql

-- 使用网络共享路径备份
BACKUP DATABASE DatabaseName
TO DISK = '\\RemoteServer\Backup\DatabaseName.bak'
WITH COMPRESSION, CHECKSUM;

-- 确保 SQL Server 服务账户有访问远程共享的权限
-- 或者使用映射驱动器（不推荐，因为服务重启后映射可能丢失）

如何备份到Azure Blob存储？

解答：

sql

-- 备份到Azure Blob存储
BACKUP DATABASE DatabaseName
TO URL = 'https://myaccount.blob.core.windows.net/mycontainer/DatabaseName.bak'
WITH CREDENTIAL = 'MyAzureCredential', COMPRESSION, CHECKSUM;

-- 创建Azure Blob存储凭证
CREATE CREDENTIAL MyAzureCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=bfqt&srt=sco&sp=rwdlacupx&se=2023-12-31T23:59:59Z&st=2023-12-01T00:00:00Z&spr=https&sig=...';

如何实现备份加密？

解答：

sql

-- 创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

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

-- 使用证书加密备份
BACKUP DATABASE DatabaseName
TO DISK = 'D:\Backup\DatabaseName_Encrypted.bak'
WITH COMPRESSION, CHECKSUM, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCertificate);

如何恢复加密的备份？

解答：

sql

-- 确保恢复目标服务器上有相同的证书
-- 如果没有，需要从源服务器备份并恢复证书

-- 备份证书
BACKUP CERTIFICATE BackupCertificate TO FILE = 'D:\Backup\BackupCertificate.cer'
WITH PRIVATE KEY (FILE = 'D:\Backup\BackupCertificate.pvk', ENCRYPTION BY PASSWORD = 'StrongPassword123!');

-- 恢复证书
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
CREATE CERTIFICATE BackupCertificate FROM FILE = 'D:\Backup\BackupCertificate.cer'
WITH PRIVATE KEY (FILE = 'D:\Backup\BackupCertificate.pvk', DECRYPTION BY PASSWORD = 'StrongPassword123!');

-- 恢复加密的备份
RESTORE DATABASE DatabaseName
FROM DISK = 'D:\Backup\DatabaseName_Encrypted.bak'
WITH REPLACE;

如何实现事务日志备份的自动切换？

解答：

sql

-- 使用 SQL Server Agent 作业调度事务日志备份
-- 作业步骤：
-- 1. 检查数据库恢复模式
-- 2. 如果是完整或大容量日志恢复模式，执行事务日志备份
-- 3. 记录备份结果

-- 示例：检查恢复模式并执行事务日志备份
DECLARE @DatabaseName SYSNAME = 'DatabaseName';
DECLARE @RecoveryModel VARCHAR(20);

SELECT @RecoveryModel = recovery_model_desc FROM sys.databases WHERE name = @DatabaseName;

IF @RecoveryModel IN ('FULL', 'BULK_LOGGED')
BEGIN
    -- 执行事务日志备份
    DECLARE @FileName VARCHAR(255) = 'D:\Backup\' + @DatabaseName + '_Log_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.trn';
    
    BACKUP LOG @DatabaseName TO DISK = @FileName WITH COMPRESSION, CHECKSUM;
END;

如何实现差异备份的自动切换？

解答：

sql

-- 使用 SQL Server Agent 作业调度差异备份
-- 作业步骤：
-- 1. 检查上次完整备份时间
-- 2. 如果上次完整备份时间在指定范围内，执行差异备份
-- 3. 记录备份结果

-- 示例：检查上次完整备份时间并执行差异备份
DECLARE @DatabaseName SYSNAME = 'DatabaseName';
DECLARE @LastFullBackup DATETIME;

-- 获取上次完整备份时间
SELECT @LastFullBackup = MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = @DatabaseName AND type = 'D';

-- 如果上次完整备份在24小时内，执行差异备份
IF @LastFullBackup >= DATEADD(HOUR, -24, GETDATE())
BEGIN
    -- 执行差异备份
    DECLARE @FileName VARCHAR(255) = 'D:\Backup\' + @DatabaseName + '_Diff_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.bak';
    
    BACKUP DATABASE @DatabaseName TO DISK = @FileName WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;
END;

如何实现数据库镜像的备份策略？

解答：

主数据库：
- 执行完整备份、差异备份和事务日志备份
- 备份策略与常规数据库相同
镜像数据库：
- 不需要单独备份，因为主数据库的事务会自动同步到镜像数据库
- 但需要定期备份镜像数据库的事务日志，以防主数据库故障
见证服务器：
- 不需要备份，因为见证服务器只用于仲裁

如何实现Always On可用性组的备份策略？

解答：

在可用性组中配置备份首选项：
- 可以选择只在主副本上备份
- 或者只在辅助副本上备份（推荐，减少主副本负载）
- 或者首选辅助副本，然后是主副本
备份策略：
- 完整备份：可以在主副本或辅助副本上执行
- 差异备份：只能在主副本上执行
- 事务日志备份：可以在主副本或辅助副本上执行
使用SQL Server Agent作业在可用性组中执行备份，作业会自动遵循备份首选项

总结

本文详细介绍了SQL Server备份恢复命令，包括各种备份类型、恢复场景、备份策略和最佳实践。通过掌握这些命令和策略，DBA可以确保数据库的安全性和可用性，在发生故障时能够快速恢复数据库，减少数据丢失和停机时间。

备份恢复是DBA的核心职责之一，建议DBA定期测试恢复流程，确保备份策略的有效性。同时，随着SQL Server版本的不断更新，新的备份恢复功能不断引入，DBA需要持续学习，更新备份恢复策略，以适应新的技术和需求。

SQLServer 备份恢复命令 ​

备份命令 ​

完整备份 ​

差异备份 ​

事务日志备份 ​

文件和文件组备份 ​

部分备份 ​

恢复命令 ​

完整恢复（简单恢复模式） ​

完整恢复 + 事务日志恢复（完整恢复模式） ​

时间点恢复 ​

页级恢复 ​

文件和文件组恢复 ​

部分恢复 ​

恢复到备用服务器 ​

备份集和备份设备管理 ​

查看备份设备 ​

查看备份集信息 ​

备份恢复策略示例 ​

完整备份 + 差异备份 + 事务日志备份策略 ​

灾难恢复场景恢复示例 ​

数据库迁移场景恢复示例 ​

版本差异 ​

SQL Server 2012及以前 ​

SQL Server 2014 ​

SQL Server 2016 ​

SQL Server 2017 ​

SQL Server 2019 ​

SQL Server 2022 ​

备份恢复最佳实践 ​

备份最佳实践 ​

恢复最佳实践 ​

常用备份恢复脚本 ​

自动备份脚本 ​

备份验证脚本 ​

恢复演练脚本 ​

常见问题（FAQ） ​

如何确定备份文件的完整性？ ​

如何处理备份失败的情况？ ​

如何实现自动备份清理？ ​

如何备份系统数据库？ ​

如何从损坏的备份文件中恢复数据？ ​

如何监控备份和恢复进度？ ​

如何优化备份和恢复性能？ ​

如何实现跨服务器备份？ ​

如何备份到Azure Blob存储？ ​

如何实现备份加密？ ​

如何恢复加密的备份？ ​

如何实现事务日志备份的自动切换？ ​

如何实现差异备份的自动切换？ ​

如何实现数据库镜像的备份策略？ ​

如何实现Always On可用性组的备份策略？ ​

总结 ​