How to Restore Model Database in SQL Server

By: Daniel Farina   |   Updated: 2019-12-03   |   Comments (1)   |   Related: More > System Databases

Problem

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.

Solution

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.

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.

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.

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

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

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.

setup /Q /ACTION=Rebuilddatabase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=Pa$$w0rd

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

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.

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.

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

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.

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

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

Next Steps

• In this tip we were using sqlcmd, maybe you need a quick guide. If so, take a look at this tip: Introduction to SQL Server's sqlcmd utility.
• Also, if you need further resources on how to connect to SQL Server with sqlcmd you can read this tip: Connecting to SQL Server Using SQLCMD Utility.
• 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.
• If you need help with the backup and restore of master and msdb databases or you want to dig further into the backup and restore mechanics you can take 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