在 SQL Server 中，系统数据库是数据库管理系统的核心部分，存储服务器配置、元数据、系统信息以及其他关键信息。

SQL Server 系统数据库概述

SQL Server 系统数据库：

  1. master
  2. model
  3. msdb
  4. tempdb
  5. Resource（mssqlsystemresource）
  6. distribution（仅用于复制）

1. master 数据库

作用

  • 系统配置：master 数据库存储SQL Server实例的系统配置选项，这些选项可以通过 sp_configure 存储过程来查看和修改。
  • 登录信息：master 数据库中保存所有登录信息，包括SQL Server登录和Windows登录。修改或删除登录信息会直接影响到用户的连接权限。
  • 链接服务器：master 数据库中存储所有链接服务器的信息，允许SQL Server实例与其他数据库服务器进行通信。
  • 存储 SQL Server 实例的所有系统级别信息，包括登录信息、端点、链接服务器、系统配置设置等。
  • 管理数据库的元数据，如每个数据库的文件位置和大小。
  • 记录服务器的启动和停止信息。

使用场景

  • 启动 SQL Server 实例时，系统会首先加载 master 数据库。
  • 创建或删除数据库时，master 数据库会记录这些操作。
  • 查看服务器配置和元数据时，需要访问 master 数据库。

2. model 数据库

作用

  • 自定义初始化设置：通过修改model数据库，可以设置新数据库的初始大小、文件增长设置、数据库选项（如恢复模式）等。

  • 模板数据库：任何在model数据库中创建的对象（如表、存储过程）都会复制到所有新创建的数据库中。

  • 作为模板数据库，用于创建新的用户数据库。

  • 所有新创建的数据库都会复制 model 数据库的结构和初始内容。

使用场景

  • 创建新的数据库时，系统会基于 model 数据库的结构和内容创建新的数据库。
  • 自定义 model 数据库可以影响所有新创建的数据库的初始设置。

3. msdb 数据库

作用

  • SQL Server 代理作业：msdb数据库存储所有SQL Server代理作业的定义和调度信息。作业可以包括备份数据库、执行T-SQL脚本、运行SSIS包等。

  • 维护计划：msdb数据库中存储所有维护计划的定义，这些计划可以帮助自动化常见的数据库维护任务，如数据库完整性检查、索引重组和更新统计信息。

  • 数据库邮件：msdb数据库中配置和管理数据库邮件功能，允许SQL Server发送邮件通知。

  • 存储 SQL Server 代理服务的所有信息，包括作业、调度、警报和操作员。

  • 存储备份和还原历史记录。

  • 管理数据库邮件、服务代理和维护计划。

使用场景

  • 创建和管理 SQL Server 代理作业时，使用 msdb 数据库。
  • 查看备份和还原历史记录时，访问 msdb 数据库。
  • 配置和管理数据库邮件和维护计划时，使用 msdb 数据库。

4. tempdb 数据库

作用

  • 事务隔离：tempdb数据库在某些事务隔离级别（如快照隔离）下存储版本控制信息，以支持并发事务。

  • 性能优化：由于tempdb数据库在SQL Server实例运行期间被频繁使用，优化tempdb的性能（如使用多个数据文件、将其放置在快速存储上）可以显著提高数据库性能。

  • 存储临时对象，如临时表和临时存储过程。

  • 用于排序和哈希操作的中间结果集。

  • 提供版本存储，用于数据库快照、在线索引操作和触发器。

使用场景

  • 执行复杂查询时，使用 tempdb 数据库存储中间结果。
  • 创建和使用临时表或临时存储过程时，使用 tempdb 数据库。
  • 进行数据库快照和在线索引操作时，使用 tempdb 数据库。

5. Resource 数据库（mssqlsystemresource）

作用

  • 系统对象的定义：Resource数据库包含所有系统对象（如系统存储过程、视图、函数）的定义。用户无法直接访问该数据库，但可以通过查询系统视图来查看这些定义。

  • 升级和补丁：在SQL Server升级或应用补丁时，Resource数据库中的系统对象可能会被更新。

  • 存储 SQL Server 的系统对象，如系统存储过程和视图。

  • 作为只读数据库，用户无法直接访问或修改。

  • 在 SQL Server 2016 及之后的版本系统对象的定义不再存储在单独的 Resource 数据库中，而是整合到 master 数据库中。SQL Server 的升级和补丁过程也有所简化，系统对象的更新直接应用到 master 数据库。简化数据库管理和维护。

使用场景

  • 升级 SQL Server 时，系统会更新 Resource 数据库中的系统对象。
  • 查询系统对象的定义时，使用 Resource 数据库。

6. distribution 数据库（仅用于复制）

作用

  • 复制拓扑：distribution数据库存储复制拓扑的所有信息，包括发布、订阅和分发代理的状态。

  • 数据传输：在事务复制中，distribution数据库用于存储从发布服务器传输到订阅服务器的数据。

  • 存储复制代理的所有信息和历史记录。

  • 管理发布和订阅的元数据。

使用场景

  • 配置和管理 SQL Server 复制时，使用 distribution 数据库。
  • 查看复制代理的状态和历史记录时，访问 distribution 数据库。

SQL Server 系统数据库的恢复方法

了解如何恢复系统数据库在灾难恢复和维护过程中至关重要。系统数据库的恢复方法：

恢复 master 数据库

恢复步骤

  1. 启动 SQL Server 到单用户模式：

    在命令提示符中输入以下命令启动 SQL Server 到单用户模式：

    net stop MSSQLSERVER
net start MSSQLSERVER /m

  2. 使用 RESTORE 命令恢复 master 数据库：

    RESTORE DATABASE master
FROM DISK = 'C:\Backups\master.bak'
WITH REPLACE;

  3. 重启 SQL Server：

    net stop MSSQLSERVER
net start MSSQLSERVER

恢复 model 数据库

恢复步骤

  1. 启动 SQL Server 到单用户模式：

    net stop MSSQLSERVER
net start MSSQLSERVER /m

  2. 使用 RESTORE 命令恢复 model 数据库：

    RESTORE DATABASE model
FROM DISK = 'C:\Backups\model.bak'
WITH REPLACE;

  3. 重启 SQL Server：

    net stop MSSQLSERVER
net start MSSQLSERVER

恢复 msdb 数据库

恢复步骤

  1. 启动 SQL Server 到单用户模式：

    net stop MSSQLSERVER
net start MSSQLSERVER /m

  2. 使用 RESTORE 命令恢复 msdb 数据库：

    RESTORE DATABASE msdb
FROM DISK = 'C:\Backups\msdb.bak'
WITH REPLACE;

  3. 重启 SQL Server：

    net stop MSSQLSERVER
net start MSSQLSERVER

恢复 tempdb 数据库

恢复步骤

tempdb 数据库在每次 SQL Server 实例启动时都会重新创建，因此无需手动恢复。如果 tempdb 数据库损坏，重启 SQL Server 实例即可解决问题。

恢复 Resource 数据库

恢复步骤

Resource 数据库是只读的，用户无法直接备份或恢复。如果 Resource 数据库损坏，一般需要重新安装或修复 SQL Server。

恢复 distribution 数据库

恢复步骤

  1. 使用 RESTORE 命令恢复 distribution 数据库：

    RESTORE DATABASE distribution
FROM DISK = 'C:\Backups\distribution.bak'
WITH REPLACE;

  2. 重启 SQL Server：

    net stop MSSQLSERVER
net start MSSQLSERVER

总结

SQL Server 的系统数据库在数据库管理和维护中起着至关重要的作用。了解作用、使用场景以及故障恢复，对于确保SQL Server实例的稳定性和可靠性至关重要。