SQL Server系统数据库–模型数据库

介绍 (Introduction)

This is my fourth article about SQL Server system databases. In previous articles of the series, I wrote about the tempdb database, the master database and the msdb database.

这是我关于SQL Server系统数据库的第四篇文章。 在本系列的前几篇文章中，我写了有关tempdb数据库， master数据库和msdb数据库的文章。

This article focuses on the model database, the last this series about SQL Server system databases. In each SQL Server instance you will find at least the next system databases:

本文重点介绍模型数据库，这是有关SQL Server系统数据库的最后系列文章。 在每个SQL Server实例中，您至少会找到以下系统数据库：

模型用法 (Model usage)

SQL Server uses the model database as a template to create new databases.

SQL Server使用模型数据库作为模板来创建新数据库。

Creating user objects in the model database is possible, but remember that after doing that every new database that will be created will have those objects as well, meaning that new databases inherit everything (to be accurate, mostly everything as you will see forward) from the model database.

可以在模型数据库中创建用户对象，但是请记住，在此之后，将要创建的每个新数据库也将具有这些对象，这意味着新数据库将从以下对象继承所有内容（准确地说，基本上是您将看到的所有内容）： 模型数据库。

Note: tempdb is also created by using the model database as a template so keep in mind that any object created in the model database it will exist in the tempdb on the next time that SQL Server instance restarts since tempdb is recreated every time the SQL Server service starts.

注意： tempdb也是通过使用模型数据库作为模板来创建的，因此请记住，在模型数据库中创建的任何对象在下次SQL Server实例重新启动时都会存在于tempdb中 ，因为每次SQL Server都会重新创建tempdb服务启动。

客制化 (Customization)

When performing a configuration change in the model database it will affect the new databases created after the configuration change so, as a DBA, is always a good practice to change some of the default settings of the model database to ensure that new databases will follow a desired customization policy.

在模型数据库中执行配置更改时，它将影响配置更改后创建的新数据库，因此，作为DBA，更改模型数据库的某些默认设置以确保新数据库遵循以下规则始终是一个好习惯。所需的自定义策略。

For example, the default Recovery Model for this system database is Full. If you change it to Simple, the next database that you will create will be created with the Simple Recovery Model. This is a good change to perform on non-Production SQL Server instances when you do not need to restore to a certain point in time.

例如，此系统数据库的默认恢复模型为“完整”。 如果将其更改为“简单”，则将使用“简单恢复模型”创建要创建的下一个数据库。 当您不需要还原到某个特定时间点时，这是对非生产型SQL Server实例执行的很好的更改。

Besides the above example of changing the Recovery Model you can also configure the data and transaction log files to make new databases created with a minimum file sizes and with an auto growth value that guarantee a minimal impact of the large number of Virtual Log Files (VLF).

除了上述更改恢复模型的示例之外，您还可以配置数据和事务日志文件，以创建具有最小文件大小和自动增长值的新数据库，以确保对大量虚拟日志文件（VLF）的影响最小）。

These configuration customizations will be only used as default values for the CREATE DATABASE command. You can always overwrite them by providing a different value (the tempdb database is always created with Simple Recovery Model no matter the Recovery Model set in the model database).

这些配置定制将仅用作CREATE DATABASE命令的默认值。 您始终可以通过提供一个不同的值来覆盖它们（ tempdb数据库始终使用简单恢复模型创建，而不管模型数据库中设置的恢复模型如何）。

The following is an example of how to create a new user database in SQL Server 2016 by overwriting the default values existing in the model database for this SQL Server version. In this example, I am providing an initial file size of 64MB instead of the default 8MB, with 128MB for the file auto growth instead of the default 64MB and then set the database to a Simple Recovery Model instead of the default Full Recovery Model:

以下是如何通过覆盖此SQL Server版本的模型数据库中存在的默认值在SQL Server 2016中创建新用户数据库的示例。 在此示例中，我提供的初始文件大小为64MB，而不是默认的8MB，其中128MB用于文件自动增长，而不是默认的64MB，然后将数据库设置为简单恢复模型，而不是默认的完全恢复模型：

 
CREATE DATABASE [NewDB]
 ON  PRIMARY 
( NAME = N'NewDB', FILENAME = N'S:\Program Files\Microsoft SQL 
Server\MSSQL13.MSSQL2016\MSSQL\DATA\NewDB.mdf' , SIZE = 65536KB , FILEGROWTH = 131072KB )
 LOG ON 
( NAME = N'NewDB_log', FILENAME = N'S:\Program Files\Microsoft SQL 
Server\MSSQL13.MSSQL2016\MSSQL\DATA\NewDB_log.ldf' , SIZE = 65536KB , FILEGROWTH = 
131072KB )
GO
ALTER DATABASE [NewDB] SET RECOVERY SIMPLE
 

Note: One of the configuration changes that do not affect the new databases is the Read-Only status. You can set the model database to a Read-Only nut the new databases will be always created without this status, i.e. with Read-Only = False.

注意：不影响新数据库的配置更改之一是“只读”状态。 您可以将模型数据库设置为只读螺母，新数据库将始终在没有此状态的情况下创建，即使用只读= False。

运作方式 (Operations)

权限 (Permissions)

Regular users by default, cannot access the model database. If they need to access to the model database, keep in mind that the user will be also have the same permissions on any new databases that will be created since the CREATE DATABASE will use the model database as a template.

默认情况下，普通用户无法访问模型数据库。 如果他们需要访问模型数据库，请记住，由于CREATE DATABASE会将模型数据库用作模板，因此该用户还将对将要创建的任何新数据库具有相同的权限。

Backups

后备

Usually changes in the model database only occur when a customization is performed. When any of these changes occurs, it is also recommended to perform a backup of the model database.

通常，仅在执行定制时才发生模型数据库中的更改。 当发生任何这些更改时，还建议对模型数据库执行备份。

Alternatively, you can have regular backups of the model database as part of the regular system databases backup plan.

或者，您可以将模型数据库的常规备份作为常规系统数据库备份计划的一部分。

移动文件位置 (Move file locations)

Model data and transaction log files can be moved to another location if and when needed to. The following code is an example on how to move the default data and transaction log files into a new location. In this case I have defined the folder S:\SysDB:

如果需要，可以将模型数据和事务日志文件移动到另一个位置。 以下代码是有关如何将默认数据和事务日志文件移动到新位置的示例。 在这种情况下，我定义了文件夹S：\ SysDB：

 
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'S:\SysDB\Data\ model.mdf');
GO
ALTER DATABASE model 
MODIFY FILE (NAME = modellog, FILENAME = 'S:\SysDB\Log\ modellog.ldf');
 

After executing the above command, stop the respective SQL Server instance service and copy the model default data and transaction log files into the new folder location. When the copy finishes successfully, the respective SQL Server service can be started. After that verify if there are no issues with the database.

执行上述命令后，停止相应SQL Server实例服务，并将模型默认数据和事务日志文件复制到新文件夹位置。 复制成功完成后，可以启动相应SQL Server服务。 之后，请验证数据库是否没有问题。

限制条件 (Restrictions)

The model database has restrictions. I will explain some of them.

模型数据库有限制。 我将解释其中的一些。

删除数据库 (Drop database)

The model database cannot be dropped. If you try to drop it an error will be raised informing you that a system database cannot be dropped:

不能删除模型数据库。 如果尝试删除它，将引发错误，通知您不能删除系统数据库：

Set offline

设为离线

Setting the model database offline is not allowed. If you try to do it the related error will be raised:

不允许将模型数据库设置为脱机。 如果您尝试执行此操作，则会引发相关错误：

Database rename

数据库重命名

Renaming the model database is not possible. When trying to do it the respective error will be raised informing you that action is not allowed:

重命名模型数据库是不可能的。 尝试执行此操作时，将引发相应的错误，通知您不允许采取的措施：

Change database owner

变更资料库拥有者

Changing the owner of the model database is not possible. When trying to do it the respective error will be raised informing you that action is not allowed:

无法更改模型数据库的所有者。 尝试执行此操作时，将引发相应的错误，通知您不允许采取的措施：

Change Data Capture (CDC)

更改数据捕获（CDC）

Enabling the CDC feature on the model database is not possible. Trying to do it the respective error will be raised informing you that action is not supported:

无法在模型数据库上启用CDC功能。 尝试执行此操作时，将引发相应错误，通知您不支持该操作：

Add filegroup

添加文件组

It is not possible to add a filegroup for the model database name. If you try to do it you will receive an error stating that adding a filegroup is not allowed on the database:

无法为模型数据库名称添加文件组。 如果尝试这样做，将会收到一条错误消息，指出不允许在数据库上添加文件组：

添加文件 (Add file)

It is not possible to add a file for the model database name. If you try to do it you will receive the error stating that cannot have files added to the database:

无法为模型数据库名称添加文件。 如果尝试这样做，将会收到错误消息，指出无法将文件添加到数据库中：

More restrictions

更多限制

There are some other restrictions that are worth to be known:

还有其他一些值得注意的限制：

• model database’s primary file group cannot be set to READ_ONLY status (but the database can); 模型数据库的主文件组设置为READ_ONLY状态（但是数据库可以）；
• model database does not allow to rename the primary file group; 模型数据库不允许重命名主文件组。
• model database cannot be deleted; 模型数据库的主文件组以及主数据和主日志文件；
• model database is the SQL Server instance collation and cannot be changed; 模型数据库的默认排序规则是SQL Server实例排序规则，不能更改；
• model database cannot be part of a database mirroring solution. 模型数据库不能是数据库镜像解决方案的一部分。

Previous articles in this series:

本系列以前的文章：

• Configuration, operations and restrictions of the tempdb SQL Server system database tempdb SQL Server系统数据库的配置，操作和限制
• SQL Server system databases – the master database SQL Server系统数据库–主数据库
• SQL Server system databases – the msdb database SQL Server系统数据库– msdb数据库

参考文献： (References:)

• model Database 模型数据库
• Move system databases 移动系统数据库
• Rebuild system databases 重建系统数据库

翻译自: https://www.sqlshack.com/sql-server-system-databases-model-database/