How to start MSSQL Server with corrupt model db
- by Jordan McGuigan
After moving some databases around (restoring, deleting, etc) we experienced an issue creating new databases. Specifically, When trying to create a new database MSSQL Server it failed because the "The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run". As some online solutions suggested, we tried to Start and Stop the MSSQL Service. Service would not restart because "Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive" (FYI: the drive has 100gb of free space). Tried restarting the machine the MSSQL Server is running on. When the server came back online, we received the same error. We have tried deleting tempdb.mdf and restoring the modeldb from the templates folder, but neither of these solved the issue. We are unable to connect to the database, even in single user mode. Many of the online solutions have us running SQL commands against the server, but we are unable to connect (even in single user mode) to the DB to run commands against the server.
Specific error messages:
Database 'model' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service could not be started.
A service specific error occurred: 1814.
We need the server up and running again ASAP.