Sqlserver 数据库备份与还原

一、备份

1、综合备份策略

Sqlserver 在实际应用中，通常会结合"恢复完整备份"、"恢复差异备份"、"恢复事务日志备份"三种备份类型，形成一个层次化的备份策略。
例如，可以每周进行一次完整备份，每天进行一次差异备份，每小时进行一次事务日志备份。这样的策略既可以保证数据的完整性，也能有效地平衡备份与恢复的效率和存储成本。

2、新建一个表——存储备份日志

create table DB_Backup_Log
(
    id int primary key identity(1,1),
    b_type varchar(10) check(b_type in('Full','Diff','Log')),
    b_name varchar(50),
    b_status char(1) check(b_status in('Y','N')),
    b_message varchar(50),
    b_time datetime
)

3、数据库备份

　　1）、完整备份（Full Backup）

完整备份是最基本的备份类型，它会复制数据库中的所有数据文件（包括 MDF 和 NDF 文件）和事务日志文件（LDF）。这种备份提供了数据的完整副本，可以作为恢复数据库的基础。完整备份的优点是恢复简单直接，但缺点是备份过程可能需要较长时间，且备份文件占用的存储空间较大。

--创建一个完整备份存储过程
CREATE PROCEDURE DB_Backup_Full
AS
BEGIN
    DECLARE @full_filename VARCHAR(255)
    DECLARE @sub_filename VARCHAR(255)
    DECLARE @date DATETIME

    BEGIN TRY
        -- Backup database
        set @sub_filename = 'ItalyDB_Full_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.BAK'
        SET @full_filename = 'D:\DB_Backup\' + @sub_filename
        BACKUP DATABASE ItalyDB TO DISK = @full_filename WITH INIT, FORMAT, COMPRESSION, STATS = 10
        insert into DB_Backup_Log (b_type,b_name,b_status,b_message,b_time) values ('Full',@sub_filename,'Y','Backup Successful',getdate());
    END TRY
    BEGIN CATCH
        insert into DB_Backup_Log (b_type,b_name,b_status,b_message,b_time) values ('Full',@sub_filename,'N',ERROR_MESSAGE(),getdate());
        RETURN; -- Exit the procedure if backup fails
    END CATCH

    -- Delete .bak files older than one month
    SET @date = DATEADD(MONTH, -1, GETDATE())
    EXECUTE master.dbo.xp_delete_file 0, N'D:\DB_Backup', N'bak', @date, 1
END

　　2）、差异备份（Differential Backup）

差异备份不会备份整个数据库，而是备份自上一次完整备份以来发生变化的数据部分。这意味着差异备份只包含自上次完整备份后更改的数据页。差异备份的优点是备份速度快，占用的存储空间较少。但恢复数据时，需要先恢复最近的完整备份，然后再应用最近的差异备份。

--创建一个差异备份存储过程
CREATE PROCEDURE DB_Backup_Differential
AS
BEGIN
    DECLARE @diff_filename VARCHAR(255)
    DECLARE @sub_filename VARCHAR(255)
    DECLARE @date DATETIME

    BEGIN TRY
        -- Backup database differentially
        set @sub_filename = 'ItalyDB_Diff_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.BAK'
        SET @diff_filename = 'D:\DB_Backup\' + @sub_filename
        BACKUP DATABASE ItalyDB TO DISK = @diff_filename WITH DIFFERENTIAL, INIT, FORMAT, COMPRESSION, STATS = 10
        insert into DB_Backup_Log (b_type,b_name,b_status,b_message,b_time) values ('Diff',@sub_filename,'Y','Backup Successful',getdate());
    END TRY
    BEGIN CATCH
        insert into DB_Backup_Log (b_type,b_name,b_status,b_message,b_time) values ('Diff',@sub_filename,'N',ERROR_MESSAGE(),getdate());
        RETURN; -- Exit the procedure if backup fails
    END CATCH

    -- Delete .bak differential files older than one month
    SET @date = DATEADD(MONTH, -1, GETDATE())
    EXECUTE master.dbo.xp_delete_file 0, N'D:\DB_Backup', N'bak', @date, 1
END

　　3）、事务日志备份（Transaction Log Backup）

事务日志备份是备份数据库事务日志的过程。这种备份类型允许你恢复到特定的时间点，非常适合需要高可用性和数据恢复精确度的环境。事务日志备份通常频繁进行，可以最大限度地减少数据丢失的风险。恢复时，需要首先恢复最近的完整备份或差异备份，然后依次应用所有相关的事务日志备份，直到达到所需的恢复点。事务日志备份记录自上一个备份（无论是完整备份、差异备份还是上一个事务日志备份）之后的所有数据库事务。

--创建一个事务日志备份存储过程
CREATE PROCEDURE DB_Backup_TransactionLog
AS
BEGIN
    DECLARE @log_filename VARCHAR(255)
    DECLARE @sub_filename VARCHAR(255)
    DECLARE @date DATETIME

    -- Check if it's time to start backup (every hour)
    BEGIN TRY
        -- Backup transaction log
        SET @sub_filename = 'ItalyDB_Log_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.TRN'
        SET @log_filename = 'D:\DB_Backup\' + @sub_filename
        BACKUP LOG ItalyDB TO DISK = @log_filename WITH INIT, FORMAT, COMPRESSION, STATS = 10
        INSERT INTO DB_Backup_Log (b_type, b_name, b_status, b_message, b_time) VALUES ('Log', @sub_filename, 'Y', 'Backup Successful', GETDATE());
    END TRY
    BEGIN CATCH
        INSERT INTO DB_Backup_Log (b_type, b_name, b_status, b_message, b_time) VALUES ('Log', @sub_filename, 'N', ERROR_MESSAGE(), GETDATE());
        RETURN; -- Exit the procedure if backup fails
    END CATCH

    -- Delete .trn log files older than one month
    SET @date = DATEADD(MONTH, -1, GETDATE())
    EXECUTE master.dbo.xp_delete_file 0, N'D:\DB_Backup', N'trn', @date, 1
END

4、总结:

这三种备份类型通常结合使用，形成一个全面的备份和恢复策略，以确保数据的安全性和可恢复性。完整备份提供基线，差异备份减少了数据量，而事务日志备份则提供了最细粒度的恢复能力。

5、新建数据库备份作业

下图是“完整备份”作业的全过程

SQL Server代理服务，会根据作业一直备份最新的数据库文件、同时删除超过一个月的数据库文件

二、还原

1、T-SQL 直接替换原来数据库

　　1）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

--如果数据库正在被应用程序使用，提示无法获得对数据库的独占访问权（3101），改成单用户模式
USE master;
GO
ALTER DATABASE ItalyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--如果待恢复的路径下已有数据库，添加一个REPLACE参数，可以替换原来占有的数据库文件
RESTORE DATABASE ItalyDB
FROM DISK = 'D:\DB_Backup\ItalyDB_Full_xxx.bak'
WITH NORECOVERY,REPLACE,
STATS=10,
MOVE 'ItalyDB_data' TO 'D:\DBFile\ItalyDB\ItalyDB_data.mdf',
MOVE 'ItalyDB_log' TO 'D:\DBFile\ItalyDB\ItalyDB_log.ldf';

-- 恢复差异备份
RESTORE DATABASE ItalyDB
FROM DISK = 'D:\DB_Backup\ItalyDB_Diff_xxx.bak'
WITH NORECOVERY;

-- 恢复事务日志备份
RESTORE LOG ItalyDB
FROM DISK = 'D:\DB_Backup\ItalyDB_Log_xxx.trn'
WITH NORECOVERY;

-- 完成恢复（恢复后，默认ItalyDB为多用户数据库）
RESTORE DATABASE ItalyDB WITH RECOVERY;

2、T-SQL 还原一个新的数据库

　　1）、"恢复完整备份"—>"恢复差异备份"—>"完成恢复"

--恢复完整备份
RESTORE DATABASE ItalyDB2
FROM DISK = 'D:\DB_Backup\ItalyDB_Full_xxx.bak'
WITH NORECOVERY,
STATS=10,
MOVE 'ItalyDB_data' TO 'D:\DBFile\ItalyDB\ItalyDB_data2.mdf',
MOVE 'ItalyDB_log' TO 'D:\DBFile\ItalyDB\ItalyDB_log2.ldf';

-- 恢复差异备份
RESTORE DATABASE ItalyDB2
FROM DISK = 'D:\DB_Backup\ItalyDB_Diff_xxx.bak'
WITH NORECOVERY;

-- 完成恢复（恢复后，默认ItalyDB为多用户数据库）
RESTORE DATABASE ItalyDB2 WITH RECOVERY;

　　2）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

--恢复完整备份
RESTORE DATABASE ItalyDB3
FROM DISK = 'D:\DB_Backup\ItalyDB_Full_xxx.bak'
WITH NORECOVERY,
STATS=10,
MOVE 'ItalyDB_data' TO 'D:\DBFile\ItalyDB\ItalyDB_data3.mdf',
MOVE 'ItalyDB_log' TO 'D:\DBFile\ItalyDB\ItalyDB_log3.ldf';

-- 恢复差异备份
RESTORE DATABASE ItalyDB3
FROM DISK = 'D:\DB_Backup\ItalyDB_Diff_xxx.bak'
WITH NORECOVERY;

-- 恢复事务日志备份
RESTORE LOG ItalyDB3
FROM DISK = 'D:\DB_Backup\ItalyDB_Log_xxx.trn'
WITH NORECOVERY;

-- 完成恢复
RESTORE DATABASE ItalyDB3 WITH RECOVERY;

三、案例

1、数据库崩溃和数据库恢复

问题：数据库每周日晚上10:00进行完整备份，每天晚上10:00进行差异备份，每小时进行一次事务日志备份。数据库在周五下午1:30崩溃，周五下午2:00进行数据库恢复，如何进行数据库恢复？

　　数据库恢复步骤："恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

1、使用最近的完整备份文件：上周日晚上10:00的完整备份，它提供了那个时间点的数据库完整快照。

2、使用最近的差异备份文件：前一天晚上（周四）10:00的差异备份，因为周五晚上10:00的差异备份尚未进行。这个差异备份包含了从上周日晚上10:00到周四晚上10:00之间的所有数据变更。（差异备份文件是依据最近的完整备份文件的）

每天晚上10:00进行的差异备份记录了自上一个完整备份以来所有的数据变化：

周二晚上10:00的差异备份：包含了从上周日晚上10:00到周二晚上10:00之间的所有数据变更；

周四晚上10:00的差异备份：包含了从上周日晚上10:00到周四晚上10:00之间的所有数据变更；

3、使用事务日志备份：从周四晚上10:00之后到周五下午1:30崩溃时刻之间的事务日志备份。这些备份每小时进行一次，因此需要依次应用从周四晚上11:00到周五下午1:00的事务日志备份。

周四晚上11:00的事务日志备份是基于周四晚上10:00的差异备份之后的数据库状态进行的备份，12:00的事务日志备份是基于11:00的事务日志备份...

通过这三个步骤，可以将数据库恢复到尽可能接近崩溃时刻的状态（恢复到周五下午1:00），确保数据的完整性和最小的数据丢失。

补充：在 SQL Server 中，事务日志备份是连续的，并且不会重叠。每个事务日志备份包含自上一个事务日志备份结束时刻之后到当前备份开始时刻之间的所有事务日志记录。

因此：

11:00的事务日志备份包含的是从10:00之后（不包括10:00整点的事务）到11:00整点之前的所有事务日志。
12:00的事务日志备份包含的是从11:00之后（不包括11:00整点的事务）到12:00整点之前的所有事务日志。
这样的设计确保了每个事务只被备份一次，没有重复部分。每个备份的起始点是上一个备份的终点，确保了数据的完整性和连续性。

四、使用SSMS还原数据库

结果如下：

1、还原原数据库详细步骤

A、右键数据库，“还原数据库”

B、选择“设备”

C、添加数据库备份文件

D、全选备份文件和添加后，SSMS会自动选择能够备份到最新日期所需的数据库备份文件

E、根据需要选择要恢复的时间点

F、还原数据库，保证目标数据库名和原数据库名相同。（如果只是备份一个数据库用来测试等等，需要修改目标数据库名，不能和现有数据库名重名）

G、备份文件还原后的数据库文件路径设置

H、在数据库还原过程中，如果目标数据库与备份文件中的数据库同名，勾选这个选项会关闭目标数据库中已有的连接，以便顺利进行数据库还原操作。这样可以确保数据库还原操作不受其他连接的影响，避免可能的数据丢失或冲突情况。

结尾日志备份：

　　通常情况下，“还原前进行结尾日志备份”这个选项在使用源数据库备份来创建新的数据库时才需要勾选。选择这个选项时，在还原操作过程中系统会先对源数据库进行最终的事务日志备份，以确保将所有未提交的事务都应用到新的数据库中。一旦勾选了该选项并完成了还原操作，系统会自动使用源数据库的结尾日志备份文件来确保新的数据库处于与源数据库相同的状态，包括最后一个备份点之后的所有事务记录。这有助于保证新的数据库数据的完整性和准确性。

　　勾选 “保持源数据库处于正在还原状态” 选项会使数据库在还原操作完成后仍然保持在“RESTORING”状态，这意味着数据库不能被用来进行正常的操作。这通常用于当你计划进行多阶段的还原操作时，例如在进行完整备份还原后，接着还原差异备份或事务日志备份。

I、还原成功

2、使用备份文件还原一个新的数据库（新数据名为Italy2）

A、设置目标数据库名与备份文件中的数据库名不同

B、目标数据库的“路径+文件名”，确保与备份文件中的“路径+文件名”不同

C、这里不需要勾选

D、成功还原了一个新的数据库

五、源 “数据库” 和 “设备” 的关系

　　我理所当然的认为，在还原数据库的时候，如果源选择 “数据库”，那么下拉框中应该会出现左侧所有的数据库。其实这种理解是错误的！

在 SQL Server Management Studio (SSMS) 中进行数据库还原时，选择“数据库”和“设备”作为源的区别主要在于备份数据的来源：

1. **数据库**：

当你在 SQL Server Management Studio (SSMS) 中选择“数据库”作为还原源时，SQL Server 会自动从 msdb 系统数据库中的备份历史记录中查找该数据库的可用备份文件。
这意味着你不需要手动指定备份文件，SQL Server 会根据备份历史记录自动选择最近的备份文件来进行还原。
适用于你想要还原的数据库仍然存在于 SQL Server 实例中，并且 SQL Server 能够找到该数据库的备份历史记录。

优点：

简化了操作，SQL Server 自动管理备份文件的选择。
适合还原最近的备份，尤其是当你不确定备份文件的具体位置时。

缺点：

如果备份文件不在 SQL Server 的备份历史记录中（例如，备份文件已被移动或删除），你将无法使用此选项。
如果你想还原特定的备份文件（例如，较早的备份），此选项可能不适合。

2. **设备**：
- 选择“设备”作为源时，你需要指定一个备份设备，这可以是磁盘文件（如 `.bak` 或 `.trn` 文件）或磁带设备。这里的“设备”实际上指的是存储备份的物理或逻辑设备。
- 使用“设备”选项时，你需要手动浏览并选择具体的备份文件。这种方式提供了更高的灵活性，尤其是在处理跨服务器迁移或从外部来源接收的备份文件时。

当你选择“设备”作为还原源时，你需要手动指定备份文件的路径（例如，磁盘文件、磁带设备等）。
这种方式适用于你已经知道备份文件的具体位置，或者你想要还原的备份文件不在 SQL Server 的备份历史记录中。
你可以选择本地磁盘上的 `.bak` 、`.trn` 文件，或者其他存储设备上的备份文件。

优点：

你可以完全控制要还原的备份文件，适合还原特定的备份。
适用于备份文件不在 SQL Server 的备份历史记录中，或者你想从外部设备还原备份。

缺点：

需要手动选择备份文件，操作稍微复杂一些。
如果你不确定备份文件的位置，可能会比较麻烦。

总结

选择“数据库”：SQL Server 会自动从备份历史记录中选择备份文件，适合还原最近的备份，且数据库的备份历史记录仍然存在。
选择“设备”：你需要手动指定备份文件，适合还原特定的备份文件，或者当备份文件不在 SQL Server 的备份历史记录中时使用。

两者的主要区别在于备份文件的选择方式：**“数据库”是自动的，而“设备”**是手动的。选择“数据库”作为源更适合日常的备份和还原操作，因为它依赖于 SQL Server 管理的备份历史和策略。而选择“设备”作为源则适用于需要从特定文件进行还原的场景，例如灾难恢复或数据迁移。

posted @ 2024-07-05 13:58 pandora2050 阅读(1878) 评论(0) 编辑收藏举报

刷新页面返回顶部

pandora2050

Sqlserver 数据库备份与还原

一、备份

1、综合备份策略

2、新建一个表——存储备份日志

3、数据库备份

1）、完整备份（Full Backup）

2）、差异备份（Differential Backup）

3）、事务日志备份（Transaction Log Backup）

4、总结:

5、新建数据库备份作业

二、还原

1、T-SQL 直接替换原来数据库

1）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

2、T-SQL 还原一个新的数据库

1）、"恢复完整备份"—>"恢复差异备份"—>"完成恢复"

2）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

三、案例

1、数据库崩溃和数据库恢复

四、使用SSMS还原数据库

1、还原原数据库详细步骤

2、使用备份文件还原一个新的数据库（新数据名为Italy2）

五、源 “数据库” 和 “设备” 的关系

总结

公告

　　1）、完整备份（Full Backup）

　　2）、差异备份（Differential Backup）

　　3）、事务日志备份（Transaction Log Backup）

　　1）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"

　　1）、"恢复完整备份"—>"恢复差异备份"—>"完成恢复"

　　2）、"恢复完整备份"—>"恢复差异备份"—>"恢复事务日志备份"—>"完成恢复"