rebuilding master database in sql server

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.

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. You won't get any error message, but you are not going to get it started.

Now in SQL Server Management Studio (SSMS) try to start SQL Server by right-clicking the root node in Object Explorer and selecting Start. 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. mdf and modellog.ldf files you have in the folder c:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA. Then stop the SQL service and rename the model. To simulate this scenario, start by doing a backup of the 3 system databases, master, model and msdb. You can’t just restore the model in the normal way even if you do have a backup of it. But if your SQL Server won't start how can you restore the model? That is the big problem we face here. So you need to restore the model from your last backup to get back to normal. Your SQL Server won't start without the model because it is required to generate the tempdb.

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

Rebuilding master database in sql server