Replace Model SQL Server Database with a User Database

By: Daniel Farina | Updated: 2019-12-13 | Comments | Related: More > System Databases

Problem

You have read that amongst the five SQL Server system databases (master, model, msdb, tempdb and mssqlsystemresource), the model database is used as a template for new databases including tempdb. Because of this, you wonder what would happen if you replace the model database with an arbitrary user database and how it will affect tempdb.

Solution

If we are talking about recovering the SQL Server model database, this is a non-standard approach. Based upon the fact that model database is used as a skeleton for new databases as well as for the tempdb database, I came up with the idea of what would happen if we use a regular user database as a replacement of a missing model database.

Create New User Database

The first step is to create a test database named SampleDB. You can use the script below to do it, but make sure to change the location of the files according to your environment.

USE [master]
GO
 
CREATE DATABASE [sampleDB]
 ON PRIMARY 
( NAME = N'sampleDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\sampleDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'sampleDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\sampleDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Now with the next script we create a table and put some data into it.

USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
   [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
   [EmployeeName] [varchar](50) NULL,
   [EmployeeAddress] [varchar](50) NULL,
   [MonthSalary] [numeric](10, 2) NULL,
   [UserID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
   [EmployeeID] 
)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (1, N'Garrison Haney', N'381-4291 Enim, Av.', CAST(6357.00 AS Numeric(10, 2)), 1)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (2, N'Ursa Dyer', N'Ap #596-1792 Odio. St.', CAST(6168.00 AS Numeric(10, 2)), 2)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (4, N'Jack Hensley', N'363-7192 Eu, Av.', CAST(7333.00 AS Numeric(10, 2)), 4)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (5, N'Harriet Williamson', N'369-7806 Vulputate St.', CAST(5574.00 AS Numeric(10, 2)), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (6, N'Rahim Moran', N'675-1652 Venenatis Ave', CAST(9970.00 AS Numeric(10, 2)), 6)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) 
VALUES (7, N'Kirestin Ingram', N'Ap #255-8512 Adipiscing. St.', CAST(8603.00 AS Numeric(10, 2)), 7)
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO

Additionally, let's create two stored procedures.

USE SampleDB;
GO
 
CREATE OR ALTER PROCEDURE PRC_Employees_Insert
    @EmployeeName VARCHAR(50) ,
    @EmployeeAddress VARCHAR(50) ,
    @MonthSalary NUMERIC(10, 2) ,
    @UserID INT
AS
    INSERT INTO dbo.Employees ( EmployeeName, EmployeeAddress, MonthSalary, UserID )
    VALUES ( @EmployeeName, @EmployeeAddress, @MonthSalary, @UserID );
GO
 
CREATE OR ALTER PROCEDURE PRC_Employees_Update
   @EmployeeID INT,
    @EmployeeName VARCHAR(50) ,
    @EmployeeAddress VARCHAR(50) ,
    @MonthSalary NUMERIC(10, 2) ,
    @UserID INT
AS
 
UPDATE dbo.Employees
   SET EmployeeName = @EmployeeName
      ,EmployeeAddress = @EmployeeAddress
      ,MonthSalary = @MonthSalary
      ,UserID = @UserID
 WHERE EmployeeID = @EmployeeID
GO

Stop SQL Server Services

Now that we have our test environment ready, we need to stop our test instance of SQL Server by using SQL Server Configuration Manager.

Stopping the default SQL Server instance.

Rename SQL Server Model Database Files

After stopping our instance, we have to open the folder where the model database files are located. Please consider that the location depends on your SQL Server version and the choices you made during the installation of your instance. But for the purpose of this tip I will assume the default location of SQL Server for data and log files which is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA. In that folder we will find the two files named model.mdf and modellog.ldf. We have to rename those files to ORIGINAL_model.mdf and ORIGINAL_modellog.ldf respectively.

If you open a command window on that folder you can rename the files using the following command.

ren model.mdf ORIGINAL_model.mdf
ren modellog.ldf ORIGINAL_modellog.ldf

The screen capture below shows the execution of the renames in my console window.

Saving a copy of the original model database of this SQL Server isntance.

Try to Restart SQL Server without the Model Database

At this point the test instance doesn't have a model database. You can try to start it and you will see that it won't start. In order to confirm that the reason of this failure is that we removed the model database files by changing their name we can open the Event Viewer application, and if we take a look at the Application log we will find out that there is an error event related to our instance of SQL Server. The error in question lets us know that SQL Server was unable to open the model.mdf file as shown in the screen capture below.

Verifying that SQL Server database missing model

Replace Model Database Files with User Database Files

Next, we have to copy the files of the database we created and name them as follows:

Source	Destination
sampleDB.mdf	model.mdf
sampleDB_log.ldf	modellog.ldf

If you open a command shell at the folder where the SQL Server data and log files are you can execute the following commands to do the work.

copy sampleDB.mdf model.mdf
copy sampleDB_log.ldf modellog.ldf

Then restart SQL Server and you will notice that it starts up successfully.

Review New Model Database Objects

When we take a look at the model database, we see the table and stored procedures that were created in the sample database.

The model database now have the objects of SampleDB database.

Review TempDB Database Objects

As the image above shows, the model database contains the same objects we created in SampleDB. This should not surprise you because we are using an exact copy of SampleDB. But you may be surprised when we take a look at the tempdb database in the next screen capture.

The TempDB database now have the objects of SampleDB database.

As can be seen in the image above, the tempdb database contains the Employees table and both stored procedures we created in the SampleDB database. The reason behind it is that the tempdb database is re-created every time SQL Server is started and in order to do so, SQL Server uses the model database as a template.

The interesting facts don't end yet. If we think about what was stated in the last paragraph, we can incorrectly suppose that the file settings for the tempdb database will be altered, but as you can see in the screen capture below the tempdb database remains with its file settings unchanged.

The reason behind this is that the file settings for the tempdb database are stored in the master database and you can see them by querying the sys.master_files dynamic management view. So basically, when tempdb is recreated the first part of the database is created by copying in the contents of the model database. The rest of the tempdb database is then filled with empty pages.

But even if you didn't know this, you should not be surprised because both the file size and recovery model differ between tempdb and the model database. Remember that the model database has one data file and one log file and is in full recovery mode, meanwhile the tempdb database is in simple recovery mode.

Now let's see if the Employees table in the tempdb database contains the data we inserted early in our SampleDB database.

USE tempdb;
GO
 
SELECT * FROM dbo.Employees;

As you can see in the next image the data in the Employees table persisted.

TempDB database was created with data in the Employees table.

Conclusion

We have seen that using a user database to replace a missing model database is possible. I am not encouraging you to do so, but as a last resort this is a viable solution.

Next Steps

Don't know what system databases are? Don't worry, in this tip you will find the answer: SQL Server System Databases.
If your problem includes a scenario of restoring the master database without having a backup you can take a look at this tip: Restoring the SQL Server Master Database Even Without a Backup.
In case you need to customize how new databases are created you can read the following tip: Modifying the SQL Server Model System Database to Customize New Database Settings.
If you need help with the backup and restore of system databases or you want to dig further on the backup and restore mechanics you can read these two tutorials: SQL Server Backup Options and Commands Tutorial and SQL Server Restore Options and Commands Tutorial.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

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

View all my tips

Article Last Updated: 2019-12-13