Restoring the model database in SQL Server

 Feb 02, 2017

Let's say you do occasional backups of the system databases, master, model and msdb, and you have lost the model database. In this blog you will see what happens if the model database is not available and what you should do in such a situation.

Your SQL Server won't start without the model because it is required to generate the tempdb. So you need to restore the model from your last backup to get back to normal. But if your SQL Server won't start how can you restore the model? That is the big problem we face here. You can’t just restore the model in the normal way even if you do have a backup of it.

To simulate this scenario, start by doing a backup of the 3 system databases, master, model and msdb. Then stop the SQL service and rename the model.mdf and modellog.ldf files you have in the folder c:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA. If you are using an older version of SQL Server the technique we will use in this blog still applies, but the file paths will be slightly different.

Now in SQL Server Management Studio (SSMS) try to start SQL Server by right-clicking the root node in Object Explorer and selecting Start. You won't get any error message, but you are not going to get it started.

You can open the ERRORLOG file in c:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLLogs to see that the reason was that the data and log files for the model database could not be found.

If the backups you have for master, model and msdb are recent ones the recommended approach is to rebuild the system databases using the setup program and then do the restores of the 3 system databases. If your backups are old there is something you can do before doing the rebuild as we explain later in the blog.

To do the rebuild you open a Command Prompt window as administrator and execute the setup program as below.

setup /quiet /action=rebuilddatabase /instance=MSSQLSERVER /sqlsysadminaccounts=BUILTINAdministrators /sapwd=Pa$$w0rd

You can find setup.exe in C:Program FilesMicrosoft SQL Server130Setup BootstrapSQLServer2016.

Use the /sapwd flag only if you are running SQL in mixed security mode. MSSQLSERVER refers to the default instance. If you have a named instance use it instead.

At this stage, you would be able to start SQL in SSMS, but you wouldn't see any of the user databases or the jobs you created with SQL Agent because you are using the same master and msdb as in a brand new installation of SQL Server.

Restoring the model and msdb from backup does not require any special procedure, but for the master you have to start SQL in single-user mode.

Open a Command Prompt window as administrator, change the directory to C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBinn and execute:

sqlserver -sMSSQLSERVER -m

Open another Command Prompt window as administrator and execute:

sqlcmd –E

Now you can execute the restore command to get your old master database back.

1>RESTORE DATABASE master FROM DISK="C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackupmaster.bak" WITH REPLACE 2>GO

After restoring the master you can simply start SQL Server normally and restore the model and msdb in SSMS as you would restore any other database.

If the backups you have for the model, master and msdb are many months old and you do a rebuild of the system databases you can't specify that you just want the model to be rebuilt! This means that after you restore the master and the msdb all the changes you made after the last backup are gone. So you would have to attach all user databases created after the last backup of the master, among other changes, like new logins, configuration changes, etc.

In a scenario like this there is something you can do. Back up the master and the msdb before doing the rebuild with setup. But if you can't start SQL how can you do the backup? The answer is the trace flag 3608.

Open a Command Prompt as administrator and execute the following command:
sqlservr -sMSSQLSERVER -t3608
It doesn't matter if you use -m or not. The important thing is the -t3608. This trace flag prevents SQL Server from recovering any databases except the master. So you can start SQL Server without the model.

With SQL Server up and running you can use SQLCMD to back up the master and the msdb.

But if we succeeded in starting SQL Server why not just restore the model? The problem is that anything you try to do that would require a normal recovery to have happened will shut down the service. Unfortunately, a RESTORE is one of those! Try it to see that it fails and the instance is automatically shut down.

Luckily the BACKUP command runs fine and you can get the latest backup of the master and the msdb before going through the process we explained at the beginning.

To learn more about the administration of SQL Server we recommend the course 20762 – Developing SQL Databases .


How do your Excel skills stack up?   

Test Now  

About the Author:

Newton Godoy  

With over 17 years of in-class training experience and over 16 years of industry experience, Newton offers students a wealth of real-world technical knowledge and expertise in the areas of .NET application development, SQL Server and SharePoint Server. After spending several years lecturing as a professor, Newton found his true calling and began his career as a MCT. He worked as a technical trainer for some of Brazil’s and Australia’s largest corporate training organisations before finally finding a home with New Horizons where he is now one of our top trainers. Newton brings a thorough mentoring capability to the classroom where he can advise on technical issues and challenges often beyond the scope of the course curriculum. His combination of technical knowledge and instructor experience make him one of the most respected instructors within the IT training industry.

Read full bio
top