How to Restore Model Database in SQL Server


By:   |   Updated: 2019-12-03   |   Comments   |   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.

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.

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.

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.

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.

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.

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
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


Last Updated: 2019-12-03


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools