How to Restore Model Database in SQL Server
By: Daniel Farina | Updated: 2019-12-03 | 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.
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.
|/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.
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.
- 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.
Last Updated: 2019-12-03
About the author
View all my tips