Restoring SQL Server. Model DB Restore gotcha

If somebody would have mentioned this, I would have 3 hours back. When recovering SQL server from a situation where you have lost the model database, make sure to remove the data and log files (if any exist) from the original directories before attempting to restore the model database. Else, SQL will shut down on you without explanation every time you attempt to run the restore. The model database is very important since it's used to create the temp db so SQL can start.

The restore process is as follows:

  1. Remove old data and log files
  2. Open command prompt and start SQL server as an application (not the service) as follows:
    <installdirectory>\binn\mssqlservr.exe -c Example: "%ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -c
  3. Restore the model database using any method.
  4. Wait for SQL server to state that the temp db has been started. (the messages could vary based on your configuration)
  5. Close the command window you started SQL server in. Start the SQL server service as normal.
  6. Your SQL server should now be healthy. Restore any remaining databases.