FIX: "Database 'model' cannot be opened" error when you restart SQL Server after tail-log backup for model database

Applies to: SQL Server 2014 DeveloperSQL Server 2014 DeveloperSQL Server 2014 Enterprise

Symptoms



Assume that you back up the tail of the log for the model database that uses norecovery option in Microsoft SQL Server 2014 or SQL Server 2012, and the database is put into restoring state. When the backup is complete, you cannot restart the SQL Server service, and you receive the following error message in SQL Server error log:
<Date> <Time> spid10s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
<Date> <Time> spid10s Error: 927, Severity: 14, State: 2.
<Date> <Time> spid10s Database 'model' cannot be opened. It is in the middle of a restore.
<Date> <Time> spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
<Date> <Time> spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Cause


This issue occurs because the model database is used as the template for all databases that are created on an instance of SQL Server. If the model database is marked as not allowing for recovery status, tempdb cannot be created, and the SQL Server service cannot start.

Resolution


Service pack information

To resolve this issue, obtain Service Pack 1 for SQL Server 2014.
For more information about SQL Server 2014 Service Pack 1 (SP1), see bugs that are fixed in SQL Server 2014 Service Pack 1 .



Workaround


To work around this issue, use the following method:
  • Prevent all connections from the application to SQL Server
  • Start the SQL Server service together with trace flag 3608
  • Execute the "restore database Model with recovery" command
  • Remove trace flag 3608, and restart SQL Server

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.