SQL Server系统数据库–主数据库

最新推荐文章于 2024-07-05 20:51:02 发布

culuo4781

最新推荐文章于 2024-07-05 20:51:02 发布

阅读量2.5k

点赞数 1

文章标签：数据库 python java linux mysql

原文链接：https://www.sqlshack.com/sql-server-system-databases-the-master-database/

版权

SQL Server的主数据库master用于记录系统实例信息，如登录账户、端点和配置设置。主数据库中不应创建用户对象，建议保持静态。变动主数据库时推荐备份，且可通过SQL Server配置管理器移动其文件位置。主数据库有一些限制，如无法删除、设置离线或重命名。本文详细介绍了主数据库的使用、操作和限制。

摘要由CSDN通过智能技术生成

介绍 (Introduction)

There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:

如以下SQL Server Management Studio（SSMS）屏幕截图所示，任何SQL Server实例中至少有4个系统数据库：

master
主
model
模型
msdb
数据库
tempdb
临时数据库

This is my second article about SQL Server system databases.

这是我的第二篇有关SQL Server系统数据库的文章。

The first one was about the tempdb database. In this article I will focus on the master database.

第一个是关于tempdb数据库的。在本文中，我将重点介绍master数据库。

SQL Server中的主数据库使用 (Master database usage in SQL Server)

SQL Server uses the master database to record all information about the SQL Server instance system, like login accounts, endpoints, linked servers and configuration settings.

SQL Server使用master数据库记录有关SQL Server实例系统的所有信息，例如登录帐户，端点，链接的服务器和配置设置。

The information that a SQL Server instance needs is stored in the master database, like the information about all existing databases and the location of their data and transaction log files. If the master database does not exist or cannot be read then the SQL Server instance cannot start.

SQL Server实例所需的信息存储在master数据库中，例如有关所有现有数据库及其数据和事务日志文件的位置的信息。如果主数据库不存在或无法读取，则SQL Server实例无法启动。

Even it is possible to create user objects in master database, it is not recommended to do so. The master database should stay as static as possible. For example, in the case that master database being rebuilt, all user objects will be lost.

即使可以在master数据库中创建用户对象，也不建议这样做。主数据库应尽可能保持静态。例如，在重建主数据库的情况下，所有用户对象将丢失。

运作方式 (Operations)

权限 (Permissions)

By default all users that have access to the SQL Server instance are granted to perform SELECT operations in the master database in the behalf of the public database role. The SELECT permission can be denied for any user as it is for a regular database or even, the public database role can be revoked to control which users may query the metadata from the master database.

默认情况下，所有有权访问SQL Server实例的用户均被授予代表公共数据库角色在master数据库中执行SELECT操作的权限。可以拒绝任何用户的SELECT权限，就像常规数据库一样，甚至可以撤消公共数据库角色，以控制哪些用户可以从主数据库查询元数据。

后备 (Backups)

Usually changes in the master database only occurs when there are changes in system objects like add/changing/deleting logins, endpoints or linked servers. A change in the master database can also be caused by changes in the SQL Server instance configuration o