SQL Server 2012 实施与管理实战指南Note：数据库备份与恢复

SQL Server 2012 实施与管理实战指南Note：数据库备份与恢复

备份类型

SQL Server提供的11种主要备份方法:

分　　级 数据备份 日志备份
数据库级 完整数据库备份 仅复制完整数据库备份 差异数据库备份 （一般） 日志备份 仅复制 日志备份
文件级 完整文件备份 仅复制完整文件备份 差异文件备份
部分 完整部分备份 仅复制完整部分备份 差异部分备份

最常用的备份方法:

分　　级 数据备份 日志备份
数据库级 完整数据库备份 差异数据库备份 （一般）日志备份
文件级 完整文件备份 差异文件备份

完整数据库备份

  1. 拷贝下数据库里的所有信息
  2. 数据库在这段时间里还会发生变化，完整数据库备份还要对部分事务日志进行备份

文件备份

  1. 备份一个或多个文件或文件组中的所有数据
  2. 在完整恢复模式下，一整套完整文件备份+跨所有文件备份的日志备份=完整数据库备份
  3. 能够指定还原损坏的文件，以加快恢复速度

部分备份

  1. 默认只备份数据库的可读写部分

差异备份

  1. 要求数据库之前做过一次完整备份（基准）
  2. 捕获自基准以来发生改变的数据
  3. 便于执行频繁备份，从而降低了数据丢失的风险

事务日志备份

  1. 每个日志备份都包括创建备份时处于活动状态的部分事务日志，以及先前日志备份中未备份的所有日志记录
  2. 不间断的日志备份序列包含数据库的完整（即连续不断的）日志链

仅复制备份

防止将要做的备份会破坏现有的备份，维护日志链

仅复制完整备份

  1. 备份整个数据库内容
  2. 但不改变差异备份的基准，不影响差异备份序列

仅复制日志备份

  1. 备份当前日志文件里现有的内容
  2. 但不会清空日志文件里备份下的日志，不影响常规日志备份的序列

选择备份策略和恢复模式

简单恢复模式下的备份

简单模式下的备份和还原策略

  1. 不能做日志备份

  2. 只能将数据库恢复到最后一次备份的结尾

  3. 工作损失风险会随时间增长而增加

  4. 可加入差异备份

    1. 建立数据库备份后，后续建立差异备份

    2. 可以使用在数据量稍大，能够容忍较长时间数据丢失的数据库上

      使用差异数据库备份补充数据库完整备份

    3. 无法满足较大数据库，或不允许较长时间数据丢失的情况

完整恢复模式下的备份

使用日志备份

  1. 日志备份只拷贝上次日志备份以来的所有日志记录，开销小

  2. 可以将数据库还原到日志备份内包含的任何时点

  3. 缺点：

    1. 数量多
    2. 需要严格按照备份产生的顺序进行恢复，管理复杂

  4. 备份策略

    1. 基本

      完整恢复模式下的最简单备份策略

      在还原这3个备份前，数据库管理员必须备份活动日志（日志尾部，即尚未备份的日志）。然后还原Db_1、Log_1和Log_2，并且不恢复数据库。接着，数据库管理员还原并恢复尾（Tail）日志备份。这一步将可以把数据库恢复到故障点，从而恢复所有数据。如果灾难毁坏的是日志文件，使得尾日志不能成功备份和恢复，那这次灾难造成的数据丢失就是从Log_2以后的所有修改。

    2. 加上差异备份

      使用差异数据库备份及日志备份来补充完整数据库备份

      在第一个数据库备份完成后，每天会做一个差异数据库备份，而在工作时间进行若干日志备份。例行日志备份将工作丢失的风险降为丢失自最近日志备份之后所做的更改。

案例：完整恢复模式下的数据库备份

对于数据库Archive

  1. 全备份

    1
    BACKUP DATABASE [Archive] TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\AdvFull1.bak'

  2. 查询历史上的备份信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT DISTINCT s.first_lsn, s.last_lsn, 
    				s.database_backup_lsn, s.backup_finish_date,
    				s.type, y.physical_device_name  
    FROM msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
    WHERE  (s.database_name = 'Archive') 
    ORDER BY  s.backup_finish_date DESC;
    查询数据库的历史备份记录，显示一个全备份
    1. first_lsn备份集中第一个日志记录的日志序列号
    2. last_lsn备份集之后的下一条日志记录的日志序列号
    3. (first_lsn, last_lsn-1) 这个日志备份所包含的所有日志序列
      1. last_lsn-1 表示前面一条日志序列号，序列号不一定以1递减
    4. (first_lsn, last_lsn)表示做数据恢复时，在做roll forward（前滚）动作时，一定要遍历的LSN
    5. database_backup_lsn 标识上一次数据库做全备份的起始LSN
    6. Type标识数据库备份的类型
      1. D(数据库)， L(日志)， I(差异数据库)，F(文件或文件组)

  3. 进行一个操作之后，做日志备份

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE TESTTABLE
    (number int,
     name nvarchar(50)
    )
    INSERT INTO TESTTABLE VALUES (1, 'aaaa')
    GO
    BACKUP LOG Archive TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\AdvLog2.bak'

    再次查询备份记录，可以看到

    查询数据库的历史备份记录，显示一个新的日志备份

    新增的日志记录，而且下一次日志备份的first_lsn总是等于上一次日志备份的last_lsn，保证了连续性

  4. 进行一个操作，然后进行差异备份

    1
    2
    3
    INSERT INTO TESTTABLE VALUES (3, 'cccc')
    GO
    BACKUP DATABASE Archive TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\AdvDiff4.bak' WITH DIFFERENTIAL
    查询数据库的历史备份记录，显示一个差异备份

    可以看到新增了差异备份

  5. 在查询结果中，我们可以发现

    1. 不管是全备份还是差异备份，都不会影响LSN的序列
      1. 只要有全备份和该全备份之后的所有日志备份，即可恢复
    2. 日志备份的LSN是连续的

文件或文件组备份

完整文件备份

  1. 备份一个或多个文件或文件组中的所有数据
  2. 在完整恢复模式下，一整套完整文件备份和跨所有文件备份的日志备份合起来，等同于一个完整数据库备份
  3. 文件备份能够只还原损坏的文件，加快恢复速度
  4. 文件备份在默认情况下包含足够的日志记录，可以将文件前滚至备份操作的末尾（简单模式下必须一起备份所有读/写文件）

文件备份的优点

  1. 更快从故障恢复
  2. 增加了计划和媒体处理的灵活性
  3. 适用于超大型数据库

文件备份的缺点

  1. 管理较复杂
  2. 必须维护一组完整的文件备份，否则无法恢复整个数据库
  3. 对于完整/大容量日志恢复模式，必须维护一个或多个日志备份，这些日志备份至少涵盖第一个完整文件备份和最后一个完整备份之间的时间间隔
  4. 在完整恢复模式下，恢复一个文件组备份，需要恢复文件组备份本身，并需要依次恢复从上一次完整数据库备份后，到恢复的目标时间点为止的所有日志备份以保证同步（需要恢复的事务日志备份数量庞大）

文件备份策略

文件组备份
  1. 最好执行完整数据库备份并在第一个文件备份开始之前开始日志备份
  2. 图中可看到，在创建数据库（在t0时间）之后立即执行完整数据库备份（在t1时间），事务日志备份按照设置的适合数据库业务要求的间隔执行。

选择数据库还原方案

无论以何种方式还原数据，在恢复数据库前，SQL Server数据库引擎都会保证整个数据库在逻辑上的一致性。例如，还原一个文件以后，必须恢复完整的一套日志文件备份，以便将该文件里的事务前滚足够长度，与数据库保持一致，才能恢复该文件并使其在线。

不同恢复模式所支持的各种还原方案

还原方案 在简单恢复模式下 在完整/大容量日志恢复模式下
数据库 完整还原 这是基本的还原策略。 　数据库完整还原可能涉及完整数据库备份的简单还原和恢复。另外，完整的数据库还原还可能涉及还原完整数据库备份，以及还原和恢复差异备份 这是基本的还原策略。数据库完整还原涉及还原完整数据库备份或差异备份（如果有），以及还原所有后续日志备份（按顺序）。通过恢复并还原上一次日志备份(RESTORE WITH RECOVERY)，完成数据库完整还原,过程中数据库处于离线状态
文件还原 只能还原损坏的只读文件，但不还原整个数据库。所以实用性不是很强 能够还原一个或多个文件，而不还原整个数据库。可以在数据库处于离线状态或数据库保持在线状态（对于SQL Server 2005的某些版本）时执行文件还原。在文件还原过程中，包含正在还原的文件的文件组一直处于离线状态。其他文件组有可能被访问
页面还原 不适用 还原损坏的页面。可以在数据库处于离线状态或数据库保持在线状态（对于SQL Server 的企业版）时执行页面还原。在页面还原过程中，正在还原的页面一直处于离线状态。 　必须具有完整的日志备份链（包含当前日志文件），并且必须恢复所有这些日志备份，以使页面与当前日志文件保持一致
段落还原 按文件组级别并从主文件组和所有读写辅助文件组开始，分阶段还原和恢复数据库 按文件组级别并从主文件组开始，分阶段还原和恢复数据库

每种恢复模式支持的还原方案以及适用范围

还原操作 完整恢复模式 大容量日志恢复模式 简单恢复模式
数据还原 完整还原（如果日志可用） 某些数据将丢失 自上次完整备份或差异备份后的任何数据将丢失
时点还原 日志备份所涵盖的任何时间 日志备份包含任何大容量日志更改时不允许 不支持
文件还原* 完全支持 不完全支持 仅对只读辅助文件可用
页面还原* 完全支持 不完全支持 无
段落还原* 完全支持 不完全支持 仅对只读辅助文件可用

* 仅适用于SQL Server企业版

数据库完整还原

将一个数据库从无到有，完整地还原出来，是最常使用的还原操作。在简单情况下，还原操作只需要一个完整数据库备份、一个差异数据库备份和后续日志备份。

数据库恢复到故障点的基本步骤

数据库需满足：

  1. 恢复模式必须是完整恢复模式
    1. 如果源数据库是简单模式，则没有相应的日志备份
  2. 灾难发生前，数据库曾经做过一个完整数据库备份（或有一套完整的文件备份）。
  3. 在上次完整数据库备份后，做过的任何日志备份每个都能找到

步骤：

  1. 备份活动事务日志（也称为日志尾部）。

  2. 还原最新完整数据库备份，而且不做事务恢复RESTORE DATABASE *database_name* FROM *backup_device* WITH NORECOVERY

  3. 如果存在差异备份，则还原最新的差异备份，而不做事务恢复WITH NORECOVERY

  4. 从还原备份后创建的第一个事务日志备份开始，使用NORECOVERY依次还原日志

  5. 恢复数据库到某个时间点RESTORE DATABASE *database_name* WITH STOPAT='????????', RECOVERY。此步骤也可以与还原上一次日志备份结合使用

实例： 恢复AdventureWorks数据库，在此示例之前，数据库已经做过一个数据库完整备份和一个日志备份。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--灾难发生后，试图创建一个尾日志备份
BACKUP LOG AdventureWorks 
TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
   WITH NORECOVERY; 
GO
--从备份恢复一个全备份
RESTORE DATABASE AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
  WITH FILE=1, 
    NORECOVERY;

--从备份中恢复一个正常的日志备份
RESTORE LOG AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
  WITH FILE=2, 
    NORECOVERY;

--用STOPAT恢复尾日志备份
RESTORE LOG AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
  WITH FILE=3, STOPAT='XXXX xx:xx:xx',
   RECOVERY;
GO

完整还原优点

  1. 管理和执行都比较简单
  2. 是最广泛使用的还原方案

完整还原缺点

  1. 数据库的完整备份恢复耗时太长
  2. 完整备份大小占空间

文件还原

从文件备份和差异文件备份中还原一个或多个受损文件的基本步骤

  1. 创建活动事务日志的尾日志备份
    1. 对于离线文件还原，在文件还原之前必须先进行一次尾日志备份
    2. 对于在线文件还原，在文件还原之后必须先进行一次日志备份
  2. 从每个损坏的文件的最新文件备份还原相应文件
  3. 针对每个还原的文件，还原最近的差异文件备份（如果有）。
  4. 按顺序还原事务日志备份，从覆盖最早还原文件的备份开始，到在步骤1中创建的尾日志备份结束。

示例

数据库abc（完整恢复模式）包含三个文件组，文件组A为读/写文件组，而文件组B和C是只读的。最初，所有文件组都处于在线状态。现在文件组A中的文件a1已损坏，数据库管理员决定在数据库处于在线状态时还原该文件。

  1. 在线还原文件a1

    1
    RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY

    文件a1处于RESTORING状态，文件组A处于离线状态

  2. 进行新的日志备份以确保捕获到该文件离线时的点

    1
    2
    -- 之前曾经做过两次日志备份
    BACKUP LOG adb TO log_backup3 WITH COPY_ONLY

  3. 在线还原日志备份

    1
    2
    3
    RESTORE LOG adb FROM log_backup1 WITH NORECOVERY
    RESTORE LOG adb FROM log_backup2 WITH NORECOVERY
    RESTORE LOG adb FROM log_backup3 WITH RECOVERY

    文件a1现处于在线状态。数据库恢复完成

页面还原

页面还原用于修复隔离的损坏页。页面还原仅可以还原数据页。

不能使用页面还原的内容

  1. 事务日志
  2. 分配页：全局分配映射（GAM）页、共享全局分配映射（SGAM）页和页可用空间（PFS）页
  3. 所有数据文件的页0（文件启动页）
  4. 页1:9（数据库启动页）
  5. 全文目录（Fulltext search catalog）

使用页面还原的要求

  1. 数据库必须使用完整恢复模式。使用大容量日志恢复模式时可能不能成功。简单恢复模式无法使用这一功能。
  2. 只读文件组中的页面无法还原
  3. 还原顺序必须从完整备份、文件备份或文件组备份中恢复页面开始
  4. 页面还原需要截止到当前日志文件的连续（无截断）日志备份，并且必须恢复所有这些日志备份后，页才能恢复到当前正常状态
  5. 数据库备份和页面还原不能同时运行

页面还原的基本步骤

  1. 获取要还原的损坏页的页ID
    1. 查询msdb数据库里的suspect_pages表
    2. 查询监视事件和SQL Server errorlog文件里所报出的错误信息
  2. 从包含页的完整数据库备份、文件备份或文件组备份开始进行页面还原
  3. 应用最近的差异备份
  4. 应用后续日志备份
  5. 创建新的数据库尾日志备份
  6. 还原新的尾日志备份

示例

使用NORECOVERY还原文件B的4个损坏页。随后，将使用NORECOVERY应用两个日志备份，然后是尾日志备份（使用RECOVERY还原）。文件B的文件ID为1，损坏的页的页ID分别为57、202、916和1016。

1
2
3
4
5
6
7
8
9
10
RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
   FROM <file_backup_of_file_B> 
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup> 
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup> 
   WITH NORECOVERY; 
BACKUP LOG <database> TO <new_log_backup> 
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
GO

段落还原

段落还原包括从主文件组开始的一系列还原顺序，有时也可以从一个或多个辅助文件组开始。在还原顺序结束后，如果恢复的文件有效并且与数据库一致，则恢复的文件将能直接变为在线状态，恢复用户访问。

离线段落还原方案

  1. 数据库在部分还原顺序之后处于在线状态
  2. 尚未还原的文件组保持离线状态，而数据库必须进入离线状态后，才能继续还原没有还原的文件组

在线段落还原方案

  1. SQL Server 企业版
  2. 数据库在完成部分还原顺序后，主文件组已经被恢复，它和所有其他已恢复的辅助文件组都处于可用状态，数据库进入在线状态，用户可以访问
  3. 尚未还原的文件组保持离线状态，而后面还原这些文件组时，数据库还能保持在线状态
  4. 如果仅还原了一部分文件组，则数据库和这些文件组有关的事务可能会被挂起，影响一部分数据页面的访问

在简单恢复模式下，可以被推迟恢复的只有只读文件组。

在完整恢复模式或大容量日志恢复模式下，任何包含多个文件组的数据库都可以使用段落还原，并且可以将数据库还原到任何时间点。

段落还原顺序

  1. 部分还原顺序

    第一个RESTORE DATABASE语句必须执行以下操作：

    1. 指定PARTIAL选项（只能指定一次）
    2. 使用包含主文件组的任何完整数据库备份，还原部分备份来启动部分还原顺序。
    3. 若要还原到特定的时间点，必须在部分还原顺序中指定该时间。还原顺序的每个后续步骤都必须指定相同的时间点

  2. 文件组还原顺序会使其他文件组在线并处于与数据库一致的某个点

    1. 如果特定只读文件未损坏且与数据库一致，则该文件无须还原。
    2. 如果在文件备份创建之前，只读文件组就已处于只读状态，则该文件组无须应用日志备份，并且文件还原会跳过日志备份的应用过程。
    3. 如果文件组是读/写文件组，则必须将未中断的日志备份链应用于上一次完整还原或差异还原，文件组才能前进到当前的日志文件

段落还原的优点

  1. 能帮助让最重要的数据最先上线
  2. 能允许在数据库恢复其他文件组的时候，先前已恢复的文件组仍保持在线状态

示例

数据库adb将在发生灾难性事件后还原到一台新计算机。该数据库使用完整恢复模式。发生灾难性事件前所有文件组都处于在线状态。文件组B是只读的。必须还原所有辅助文件组，但这些辅助文件组将按重要性顺序进行还原：A最高，其次是C，最后是B。在此示例中，存在4个日志备份，其中包括尾日志备份。

  1. 备份日志尾部（是后面还原顺序中将要应用的最后一个备份）

    1
    2
    -- 数据库已损坏，因此创建尾日志备份要使用NO_TRUNCATE选项
    BACKUP LOG adb TO tailLogBackup WITH NORECOVERY, NO_TRUNCATE

  2. 还原顺序

    1. 部分还原主文件组和辅助文件组A

      1
      2
      3
      4
      5
      6
      7
      8
      RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 
         WITH PARTIAL, NORECOVERY
      RESTORE DATABASE adb FILEGROUP='A' FROM backup2 
         WITH NORECOVERY
      RESTORE LOG adb FROM backup3 WITH NORECOVERY
      RESTORE LOG adb FROM backup4 WITH NORECOVERY
      RESTORE LOG adb FROM backup5 WITH NORECOVERY
      RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
      1. 主文件组和辅助文件组A处于在线状态
      2. 文件组B和C中的所有文件都处于恢复挂起状态，处于离线状态。
      3. 步骤1中的最后一条RESTORE LOG语句的消息会指出：由于文件组C不可用，因此涉及此文件组的事务回滚已延迟

    2. 在线还原文件组C

      1
      2
      3
      4
      5
      RESTORE DATABASE adb FILEGROUP='C' FROM backup2a WITH NORECOVERY
      RESTORE LOG adb FROM backup3 WITH NORECOVERY
      RESTORE LOG adb FROM backup4 WITH NORECOVERY
      RESTORE LOG adb FROM backup5 WITH NORECOVERY
      RESTORE LOG adb FROM tailLogBackup WITH RECOVERY
      1. 主文件组及文件组A和C处于在线状态
      2. 文件组B中的文件处于恢复挂起状态，该文件组处于离线状态
      3. 解析延迟的事务后，日志被截断

    3. 在线还原文件组B

      文件组B的备份是在该文件组变为只读状态之后进行的，因此，在恢复过程中无须再恢复日志备份。

      1
      RESTORE DATABASE adb FILEGROUP='B' FROM backup2b WITH RECOVERY

      所有文件组现在都处于在线状态

    孤立用户故障排除

    SQL Server的用户安全管理

    1. 服务器层

      1. 登录账号（Login）

      2. 管理整个SQL Server服务器，开启跟踪，修改SQL Server安全配置，备份所有数据库等

      3. 所有登录账号的信息，存放在master数据库里

        1. 查询sys.server_principals

          查询sys.server_principals

      4. 每个登录账号都有一个唯一编号SID

        1. 对于Windows Login, SID就是Windows用户在域里的SID
        2. 对于SQL Login, 会随机生成SID，每次生成的SID都有可能不一样

    2. 数据库层

      1. 数据库用户（Database User）
      2. 可以设置它对这个特定的数据库有读写、修改表格结构、存储过程定义等权限
      3. 查询sys.database_principals了解用户信息
      4. 每个数据库用户除了名字，也有一个唯一的SID

    3. SQL Server登录账户必须要和某个数据库用户相对应后，才能被数据库接纳

      1. 即用户数据库sys.database_principals里的SID和master数据库sys.server_principals里的SID匹配
      2. 对于Windows登录账号，如果新的服务器和原先的服务器在同一个域里，那么同样的Windows登录账号会有一样的SID
      3. 对于SQL登录账号，当用户数据库恢复到新的服务器上后，master数据库sys.server_principals里并没有这个账号，但是用户数据库里还是有“test”这个数据库用户，于是这个用户被“孤立”了

    4. 检测孤立用户

      1
      2
      3
      USE <database_name>;
      GO; 
      EXEC sp_change_users_login 'Report';

    5. 链接孤立用户

      1. SQL登录账号

        1. 创建相应的SQL登陆账号

        2. 使用命令重新链接服务器登录账户与数据库用户，修改SID为一致

          1
          2
          3
          USE <database_name>;
          GO
          EXEC sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';

      2. Windows登录账号

        1. 如果SID不同，说明域也发生了变化
        2. Windows服务器登录账户和数据库用户是不能强制链接在一起的

系统数据库备份与恢复

每次进行过系统更新后，都必须备份多个系统数据库

有任何数据库在服务器实例上使用了复制，则还必须备份distribution系统数据库

系统数据库 说　　明 是否支 持备份 恢复 模式 注　　释
master 记录SQL Server系统的所有系统级信息的数据库 支持 简单 必须经常备份master数据库，以便根据业务需要充分保护数据。建议使用定期备份计划，这样在大量更新之后可以补充更多的备份
model 在SQL Server实例上为所有数据库创建的模板 支持 用户 可配置 仅在业务需要时备份model数据库，例如自定义其数据库选项后立即备份。 最佳方法：建议您仅根据需要创建model数据库的完整数据库备份。由于model数据库较小而且很少更改，因此无须备份日志
msdb SQL Server代理用来安排警报和作业以及记录操作员信息的数据库。msdb数据库还包含历史记录表，例如备份和还原历史记录表 支持 简单 （默认值） 更新时备份msdb数据库
Resource（RDB） 包含SQL Server附带的所有系统对象副本的只读数据库 不支持 — Resource数据库位于mssqlsy- stemresource.mdf文件中，该文件仅包含代码。因此，SQL Server不能备份Resource数据库
tempdb 用于保存临时或中间结果集的工作空间。每次启动SQL Server实例时都会重新创建此数据库。服务器实例关闭时，将永久删除tempdb数据库中的所有数据 不支持 简单 无法备份tempdb系统数据库
distribution 只有将服务器配置为复制分发服务器时才存在此数据库。此数据库存储元数据、各种复制的历史记录数据以及用于事务复制的事务 支持 简单 根据您具体的复制配置决定

master数据库

建议经常做master的完整数据库备份

Master数据库包含的信息

  1. SQL Server系统的所有系统级信息，例如登录账户、系统配置设置、端点和凭据以及访问其他数据库服务器所需的信息
  2. 记录启动服务器实例所需的初始化信息，每个其他数据库的主文件位置
  3. 是SQL Server启动的时候打开的第一个数据库

重建master数据库

  1. 如果由于master数据库损坏严重到无法启动服务器实例，又没有备份，则必须重建
  2. 重建master数据库将使所有的系统数据库恢复到其原始状态，SQL Server就像被重装过一样

导致master数据库更新并要求进行备份的操作类型

  1. 创建或删除用户数据库
    1. 用户数据库自动增长以容纳新数据时，master数据库不受影响
  2. 添加或删除文件和文件组
  3. 添加登录或其他与登录安全相关的操作
    1. 数据库层面的安全操作（如向数据库中添加用户）对master数据库没有影响
  4. 更改服务器范围的配置选项或数据库配置选项
  5. 创建或删除逻辑备份设备
  6. 配置用于分布式查询和远程过程调用（RPC）的服务器，如添加链接服务器或远程登录

恢复master数据库

  1. RESTOR
  2. 还原master数据库后，SQL Server实例将自动停止
  3. 将master数据库恢复到一台新的服务器上时，文件地址会发生变化，需要使用单用户模式启动SQL Server，将master数据库里的信息修改成新的地址
    1. 单用户模式重新启动服务器时，应首先停止所有SQL Server服务（服务器实例本身除外），并停止所有SQL Server实用工具

model数据库

  1. model数据库是SQL Server使用的模板
  2. model数据库的全部内容（包括数据库选项）都会被复制到新的数据库
  3. 不推荐做任何修改，不要把任何用户数据放在这个数据库里
  4. 还原model数据库与对用户数据库执行完整的数据库还原相同

msdb数据库

  1. 存储数据，包括计划信息以及备份与还原历史记录信息
  2. SQL Server将在msdb数据库中自动维护一份完整的在线备份与还原历史记录
  3. 默认情况下，msdb数据库使用简单恢复模式
  4. 如果在恢复用户数据库时使用msdb数据库中的备份与还原历史记录信息，建议对msdb数据库使用完整恢复模式

tempdb

  1. 每次启动SQL Server时都会重新创建tempdb数据库
  2. 不需要备份

资源数据库（Resource Database）

  1. 只读数据库
  2. 包含了SQL Server中的所有系统对象，不包含任何用户数据或用户元数据
  3. 对于同一个版本的SQL Server，它们的Resource数据库应该都是一样的
  4. 物理文件名：mssqlsystemresource.mdf和mssqlsystemresource.ldf
  5. 每个SQL Server实例都有且只有一个关联的mssqlsystemresource.mdf文件，并且实例间不共享此文件
  6. 理论上不用备份Resource数据库，但是最好做基于文件的备份或基于磁盘的备份，恢复时注意版本问题

带有FILESTREAM功能的数据库备份和恢复

对于有FILESTREAM功能的数据库，在进行备份和恢复时，会自动包含FILESTREAM数据，并不需要额外做文件级别的备份和恢复。

  1. 启动FILESTREAM

    1. 打开数据库的配置管理器，找到数据库服务，并右键点击属性。找到FILESTREAM的设置，启用FILESTREAM

    2. 执行语句

      1
      2
      EXEC sp_configure filestream_access_level, 2
      RECONFIGURE

    3. 重新启动数据库服务

  2. 创建TESTDB，指定文件组

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE DATABASE TESTFSDB
    GO

    ALTER DATABASE TESTFSDB 
    ADD FILEGROUOP [FSGroup] CONTAINS FILESTREAM
    GO

    ALTER DATABASE TESTFSDB
    ADD FILE (NAME=N’FSDATA’, FILENAME=N’D:\FSData’) TO FILEGROUP FSGroup
    GO

  3. 创建表并开启FILESTREAM功能

    1
    2
    3
    4
    5
    6
    7
    8
    use TESTFSDB
    go
    CREATE TABLE FSTable
    (
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [FileName] NVARCHAR(100) ,
    [BlobData] VARBINARY(MAX) FILESTREAM NULL
    )

  4. 插入数据

    1
    2
    3
    4
    5
    6
    7
    INSERT INTO FSTable
    SELECT NEWID(), 'File1', * FROM 
    OPENROWSET(BULK N'D:\temp\Image.jpg', SINGLE_BLOB) AS Document

    INSERT INTO FSTable
    SELECT NEWID(), 'File2', * FROM 
    OPENROWSET(BULK N'D:\temp\Image.jpg', SINGLE_BLOB) AS Document

  5. 备份

    1
    2
    BACKUP DATABASE [TESTFSDB] TO DISK='C:\temp\TESTFSDB.bak'
    BACKUP LOG [TESTFSDB] TO DISK='C:\temp\TESTFSDB.bak'

  6. 我们关闭数据库服务，并且删除整个D:。随后，重新启动数据库服务，并依据TESTFSDB.bak文件对数据库TESTFSDB进行恢复。

  7. 我们会发现，D:。并且查询表FSTable，我们能得到正确的数据。

## 应对由于备份损坏导致的还原错误

  1. 忽略错误继续执行操作

    1. 使用CONTINUE_AFTER_ERROR，使还原操作跳过错误继续进行，并还原SQL Server现在所能还原的所有内容

      1
      2
      RESTORE DATABASE database_name 
      FROM backup_device WITH CONTINUE_AFTER_ERROR, [ NORECOVERY ]

    2. 如果错误发生在一些比较关键的地方，比如某个数据文件的文件头信息，那么恢复还是有可能完全失败，数据库无法恢复

    3. 在忽略错误后，使用DBCC CHECKDB（WITH TABLOCK）修复数据库

  2. 建立备用（Standby）服务器

    1. 将做好的备份使用Log Shipping或者其他类似的机制在备用服务器上预先恢复好

实例：将数据库系统在一台新服务器上恢复

假设现在管理员遇到了下面这些挑战：

  • 原先服务器出现硬件故障，已经无法启动。需要将整个SQL Server系统紧急迁移到一台备用服务器上。（备用服务器名字叫sqlserverpc，SQL Server是默认实例。）

  • 备用服务器和原服务器不同名，SQL Server安装的路径也不一样。（备用服务器安装路径为C:FilesSQL Server.4。）

  • 因为良好的备份策略，现在手头有最新的master、msdb和model数据库备份，以及其他所有用户数据库备份。

  • 现在需要将系统数据库恢复，以还原所有数据库系统信息（用户、密码、任务等）。然后才能恢复用户数据库。

Reading
#SQL Server
SQL Server 2012 实施与管理实战指南Note：数据库备份与恢复
http://example.com/2022/07/29/SQL Server 2012 实施与管理实战指南Note：数据库备份与恢复/
Author
Jiacheng Xie
Posted on
July 29, 2022
Licensed under