What is a model database?

Do you ever take a look at SSMS in the System Databases section? People that are new to SQL Server might or might not know much about these essential System Databases. Four System Databases are required for every SQL Server to function.

Below I will outline the model database, which has a unique purpose for SQL Server.

The model database is an empty shell of a database that has the sole purpose of providing a database template for any new User Database created in SQL Server. With that being said, you can make configuration changes to the model database and then expect those baseline settings to be applied to all new user databases.

For instance, let’s say you want every new database to have a 4GB data file and a 2GB log file; you can do that.  What if you wanted all new databases to be in the Bulk Recovery mode? You can do that. What if this is my favorite, you wanted a standard set of security settings for each new database created? You guessed it; you can do that as well.

I will now set my model database’s configuration to match the four examples I outlined above and show you how that will impact newly created databases.

USE [master]
GO
ALTER DATABASE [model] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 4194304KB )
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 2097152KB )
GO
USE [model]
GO
CREATE USER [ModelUser] FOR LOGIN [ModelUser]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ModelUser]
GO

In this script, you see I set the recovery mode, the data, log size, and add a SQL user. Now each time a new database is created, it will have these settings.

This screenshot displays the results of these settings after using the SSMS GUI to create a new database.

I executed a couple of system views to show you the values assigned to the ModelDeployment1 database that I created. First, you see it is in Bulk Recovery mode. You then see that the size of the data file and log file match what we expect. Finally, you see the new SQL user ModelUser was created.

Now, if I execute a new User Database command using the

CREATE DATABASE syntax, you will see the same results for the database.

Using TSQL, I get the same results when creating a new User Database. I point out that the SSMS GUI and TSQL acted differently in years past and deployed different model settings. That has been resolved in the modern version of SSMS and SQL Server.

I hope this gives you a little behind the scenes of the System Database model and how it is used and needed to make SQL Server work.

Resources:

Microsoft Docs – model Database

The post What is a model database? appeared first on GarryBargsley.com.