SQL Server system databases – the model database

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.

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:

Model usage

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

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.

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.

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.

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).

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).

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:

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 )

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.

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.

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:

ALTER DATABASE model

MODIFY FILE (NAME = modeldev, FILENAME = 'S:\SysDB\Data\ model.mdf');

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.

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)

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:

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:

The model database’s primary file group cannot be set to READ_ONLY status (but the database can);
The model database does not allow to rename the primary file group;
The guest user, the primary file group and the primary data and primary log files of model database cannot be deleted;
The default collation for the model database is the SQL Server instance collation and cannot be changed;
The model database cannot be part of a database mirroring solution.

Previous articles in this series:

Vitor Montalvão

Vitor Montalvão is a senior SQL Server Engineer with more than 20 years of experience working with SQL Server.

He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.

Vitor also has a website with some useful information about SQL Server: https://f1-sqlserver.wixsite.com/f1-sqlserver

View all posts by Vitor Montalvão

Latest posts by Vitor Montalvão (see all)

An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database - August 18, 2017
SQL Server system databases – the model database - August 9, 2017
SQL Server system databases – the msdb database - July 14, 2017