How to Restore Model Database in SQL Server

By:   |   Comments (1)   |   Related: More > System Databases


You are facing a problem with one of your SQL Server instances. Something happened to one of the disks and the result is a damaged model database, so SQL Server won't start up. In this tip I will show how you can fix this issue.


The model database is used as the template for all new databases created on an instance of SQL Server. This is also used for the tempdb database which is created every time SQL Server is started. As a consequence, the model database must always exist on a SQL Server instance.

Restore SQL Server Model Database

Usually when we have a problem in one of our databases, the first thing that comes to mind is to check if we have a backup. We probably all agree that this is the proper first step in order to recover a database, including the system databases master and msdb, but for the model database things are different.

At first you may think having a backup of the model database will make things easier, but we will see that it is irrelevant having a model database backup in this scenario. Remember that in order to perform a restore we need the SQL Server instance up and running; and it can’t start without the model database. Luckily there is a workaround to this problem that I show below.

What will mark our course of action is whether you have a recent backup of master and msdb databases. Let’s assume that you don’t have those backups. In such case, we will need to make a backup of those databases, but the problem is that we can’t start our instance of SQL Server.

Using Trace Flag 3608 to Start SQL Server

You can use the undocumented trace flag 3608 as a startup parameter that makes SQL Server start without recovering databases. Remember that when SQL Server starts it performs a recovery of the databases in order to make them ready to be used. By using this trace flag, we will be able to start SQL Server and take a backup of the master and msdb databases.

If you are thinking about why at this point, I am not restoring a model database backup, the answer is because in order to do a restore SQL Server needs to use the Tempdb database which we won’t have because it is created using the model database as a skeleton.

The first step is to locate the binaries folder of your SQL Server instance, which you can find in the SQL Server Configuration Manager.

We can see theh binary path of our instance in SQL Server Configuration Manager.

After locating that folder, open a command window and move to the folder where the binaries are. In my case the folder is "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn".

Now execute the following command to start your instance using trace flag 3608 as a startup parameter.

sqlservr.exe -sMSSQLSERVER -t3608

The –s parameter is used to specify the instance name, which in my case is the default instance –sMSSQLSERVER. Additionally, the -t3608 argument is used to pass the trace flag 3608 to the instance startup. Notice the lower case on the –t argument, it is case sensitive.

Starting SQL Server from the console with trace flag 3608.

Backup SQL Server master and msdb database

Open another command window and login to your instance using the sqlcmd utility.  The following syntax of sqlcmd shows how to connect to an instance named ServerName using trusted authentication (the –E parameter).

sqlcmd –S ServerName –E 

After logging in, run the following code to take a backup of your master and msdb databases.

    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\master.bak';
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\msdb.bak';

The next screen capture shows the execution of the script to take a backup of master and msdb databases in my test environment.

Using sqlcmd to back up the master and msdb databases.

Rebuild SQL Server System Databases

Now that we have a backup of our system databases, master and msdb, we can safely rebuild the system database on our instance to recover the model database. In order to do so we have to execute the following command in the setup folder of your SQL Server installation or the installation media you used to install SQL Server.


In the table below you will find a brief description of each parameter in the previous command.

Parameter Description
/Q Instructs setup to run in quiet mode.
/ACTION=Rebuilddatabase Specifies that we want to rebuild the system databases.
/INSTANCENAME The name of the instance you want to rebuild the system databases. Since in my case it is the default instance I entered MSSQLSERVER.
/SQLSYSADMINACCOUNTS The windows user of groups you want to have sysadmin privileges.
/SAPWD The password of the SQL Server SA account.

Restore SQL Server master and msdb database

After the setup program completes rebuilding the system databases, we have to restore our backups of master and msdb databases in order to get the system back to where it was. To do so, we need to start SQL Server in single user mode from the console using the –m argument or –m[Program Name], where [Program Name] is the name of the client application to be used to connect to SQL Server. I suggest using -mSQLCMD argument so we instruct SQL Server to only accept a connection if the source application is SQLCMD.

sqlservr.exe –sMSSQLSERVER –mSQLCMD

On the screen capture below you will see the startup of the SQL Server instance with the parameters exposed.

Starting SQL Server from the console with -m parameter.

After SQL Server starts, open sqlcmd, connect to your instance as usual and run the next line of code to restore the master database. A word of advice, when SQL Server is started in single user mode you can only restore the master database. When the restore ends the SQL Server instance will automatically shut down.

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\master.bak' WITH REPLACE;

Now to restore the msdb database we need to start the SQL Server instance from SQL Server Configuration Manager as usual and then proceed with the restore of the msdb database.

    FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\msdb.bak' WITH REPLACE;
Using sqlcmd to restoe master and msdb databases.

At this point the SQL Server instance should be back to where it was prior to the issue.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina 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

Comments For This Article

Thursday, December 12, 2019 - 12:36:03 PM - sterling sterling Back To Top (83408)

thank you for this great article. This happened to me on my testing box, SQL 2014, model database was unable to come online, error 9003, mdf and ldf don't match, etc. I followed the steps, except, I renamed the existing master.mdf/ldf, msdb.mdf/ldf first; once system databases were rebuilt, I simply deleted the newly created master.mdf/ldf, msdb.mdf/ldf and renamed the existing files, it worked like a charm.

get free sql tips
agree to terms