SQL Server Model Database Tutorial

By: Eric Blinn | Updated: 2020-06-17 | Comments | Related: More > System Databases

Problem

SQL Server comes installed with four system databases by default. They are master, model, msdb, and TempDB. I need to know what the system database "model" is and what it is used for. These previous tutorials cover msdb and master in great detail.

Solution

This tip will cover the model database. The model database is a template. Whenever a new SQL Server database is created it is copied from the model database. This also goes for tempdb, which is regenerated on each start of the SQL Server service.

Model DB in action

To show how the model database affects the creation of a new database, the model database for this instance has been modified to use some more obscure settings. The database and log files have odd sizes and autogrowth settings. The recovery model has been set to Bulk-Logged and the compatibility mode has been changed to SQL Server 2012 -- despite being a SQL Server 2019 instance.

This screenshot shows that the model database has a data and log file of 9mb and 14mb respectively along with autogrowth of 12mb and 14mb.

This screenshot shows the aforementioned recovery model and combability level.

The next step is to create a new database without any optional parameters.

create database MyNewDB;

This screenshot shows that the file sizes and growth settings of MyNewDB match that of model.

This screenshot shows that the additional settings of MyNewDB also match that of model.

It is important to note that the copied settings are not limited to these few. They were chosen to be demonstrative.

Along with settings, user object that might be stored in model are also copied over to new databases that are created. To see that in action this script will be run. It creates a new table, puts 100 rows in it and then creates a new database. Without taking any additional steps the numbers table is able to be queried.

USE model
GO

create table dbo.numbers (i  int identity)
GO

insert into dbo.numbers default values
GO 100

use master
GO

create database MyNewDB
GO

USE MyNewDB
GO

SELECT * FROM dbo.numbers

That brand-new database already has a user table in it!

This screenshot shows the numbers table as an object in object explorer of the newly created database.

Model and TempDB

The databases model and TempDB have a special relationship. While TempDB is recreated upon every start of the SQL Server service and it is created from model, not every setting is copied. TempDB will know of any user object created and the compatibility level of model, but TempDB will always be in simple recovery model and it will remember the file count, sizes, and growth settings given to it regardless of what is done to model.

After a service restart TempDB now has the numbers table.

Even after the service restart TempDB keeps its many files and does not respect the changes made to the model sizes and growth settings.

Can I run SQL Server without a model database?

SQL Server will not allow the model database to be dropped or taken offline nor is there any reason to do that.

Is the version of the model database unique to the version, edition, and patch level of SQL Server?

The model database is blank and isn’t unique to any version or edition of SQL Server.

Can you drop or rename the model database and should you do this for any reason?

The model database cannot be dropped or renamed. There is no reason to attempt to complete such an operation.

SQL Server model Database Location

What physical files, names, and locations support the model database?

The default data file is logically called modeldev and physically called model.mdf. The log file is modellog and modellog.ldf. They are probably very small and rarely accessed to it is ok to store them with the other data and log files as they won’t use up valuable storage resources.

Where do I find the model database on disk and in SSMS?

The files can be found using the database properties box as seen earlier in this tip. They can also be found using the sys.sysfiles DMV.

SELECT * FROM model.sys.sysfiles

Can the model database be moved?

Absolutely. The database can be moved by changing the system catalog.

ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILENAME = N'F:\MSSQL\model2.mdf')

This code does not actually move any data. It simply tells SQL Server to consider this new file name and/or location (as listed in the "FILENAME" section during the next startup. The script can be repeated for each data/log file in the database. This is clearly reflected in the output of this statement.

The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

The next step is to stop the SQL Server service, move the file(s) from their current locations and names to the new one(s). Then start up the SQL Server service. It will find the files in the new location to which it had previously been alerted.

SQL Server model Database Size and Growth

How should the database and transaction log growth settings be configured for the model database?

These settings should be set to whatever default would be desirable for any new database that is created. Usually that means a number of MB growth rather than a percentage.

What is the typical size of the model database?

The size is typically only a few MB as there is no data stored in model by default. This can be modified if it makes sense to start new user databases at a larger size.

How big does the model database get?

As nothing is stored in model by default, this database should be very small and should not grow.

Server model Database Maintenance

Can you shrink the database and transaction log for the MODEL database?

You could, but as this database has no meaningful activity that is not something that should ever come up.

Should you rebuild indexes?

There shouldn’t be any tables of any consequence in model so there is no reason to run index maintenance on the model database.

Should you run integrity checks what is the code?

With the data sitting mostly static the model database should never become corrupt, but that can’t be counted on. Running a daily integrity check is a very tiny operation and one worth doing.

SQL Server model Database Objects

What are the key tables and procedures in the MODEL database that are important for SQL Server Professionals to know about?

There really aren’t any. This is a "blank" database that doesn’t come installed with any tables or procedures.

Can I store my own objects in the MODEL database?

One might consider doing this if there is a table like a numbers table that coders expect to be in any new database that could get created. Remember that any of these objects will also appear in TempDB.

Who has access to the model database and are there different levels of permissions?

Users have no permissions to model unless it is explicitly granted at the database level or inherited via a server role like sysadmin. Since there is nothing in the model database that isn’t also in TempDB or any new database there isn’t a good reason for regular users to be connecting to model.

SQL Server model Database Backup

Do I need to backup the model database?

The model database should be backed up. While there is very little information in the database being able to recover it in the event of a disaster is important as SQL Server expects it to exist.

How do I backup the model database?

The model database can be backed up the exact same way as any other database. The code below will back up the database to a local file. Consider using a location that is not local to the SQL Server -- if that is possible -- as that reduces the risk of losing the model database in the case where the server becomes unavailable.

BACKUP DATABASE MODEL TO DISK = 'C:\MODEL.bak';

How frequently do I backup the model database?

Since this database is largely static it can be backed up far less often than most databases. Even weekly or monthly might be enough. Its size is very small so backing it up daily as part of a system database maintenance plan will not be prohibitive.

What recovery model should the model database have and can the recovery model be changed?

Model is set to FULL recovery be default and it should probably stay there unless all new databases created on the instance would need to be in SIMPLE recovery.

SQL Server model Database Restore

Is there ever a need to restore the model database?

Since there should be no activity in the model database there should not be a need to restore a model database unless it somehow becomes corrupt.

How to restore the model database?

The model database is not actively used at any point during SQL Server operation so it can be restored at any time using the same steps needed to restore any user database.

Next Steps

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-06-17