As a SQL Server database administrator (DBA), you may be familiar with the system databases such as Master, Msdb, and Tempdb. However, one database that often raises questions is the Model database. It seems to just sit there, and many DBAs wonder what it is used for.

The Model database is essentially a template for all new databases created in the SQL Server instance. When a new database is created, it inherits the objects (tables, users, stored procedures, functions, etc.) from the Model database. This can be quite handy as it allows you to set up common objects that you want to be present in every new database.

However, it is important to exercise caution when making changes to the Model database. Any objects added or modified in the Model database will be replicated in every new database created. This means that if you clutter up the Model database, you are also cluttering up the Tempdb database, as Tempdb is created based on the Model database every time the SQL Server instance is restarted.

Furthermore, if the Model database becomes corrupted, it can prevent the SQL Server instance from starting up. In such cases, there are a few methods to fix a corrupted Model database:

1. If the SQL Server instance is still running, the easiest and best method is to restore the Model database from a backup, just like restoring a user database.
2. If the SQL Server instance is no longer running, you can use the REBUILDDATABASE option of the SQL Server setup to rebuild the Model database. However, this process can be complicated and should be approached with caution.
3. Another approach, although not recommended, is to restore the corrupted Model database on another SQL Server instance to an alternate location, such as “Model_fix”. Then, detach the restored database and replace the corrupted version with the restored one.

It is always a good practice to test and verify concepts before writing about them. To demonstrate how objects created in the Model database show up in the Tempdb database, I performed a simple test:

USE Model
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

USE Tempdb
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

USE Model
GO
CREATE TABLE TableInModel (
Column1 int,
Column2 varchar(30)
)

USE Model
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

USE Tempdb
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

In this test, I checked the objects in the Model database and Tempdb database before creating a new table in the Model database. After restarting the SQL Server instance, I checked the objects again in both databases. As expected, the table created in the Model database also appeared in the Tempdb database.

Understanding the purpose and functionality of the Model database is crucial for DBAs. It allows for consistent object creation in new databases and plays a role in the creation of the Tempdb database. However, it is important to exercise caution when making changes to the Model database to avoid cluttering up the Tempdb database or causing potential startup issues.

Thank you for reading! If you have any questions or would like to learn more about SQL Server concepts, feel free to leave a comment below.