在移动一些数据库（恢复、删除等）后，我们在创建新数据库时遇到了问题。具体来说，当尝试创建新的 MSSQL Server 数据库时，它失败了，因为“数据库‘model’被标记为 RESTORING，并且处于不允许运行恢复的状态”。根据一些在线解决方案的建议，我们尝试启动和停止 MSSQL 服务。服务无法重新启动，因为“无法创建 tempdb。您可能没有足够的可用磁盘空间。通过删除 tempdb 驱动器上的其他文件释放更多磁盘空间”（仅供参考：驱动器有 100gb 的可用空间）。尝试重新启动运行 MSSQL Server 的机器。当服务器重新上线时，我们收到了相同的错误。我们尝试删除 tempdb.mdf 并从 templates 文件夹恢复 modeldb，但这些都无法解决问题。即使在单用户模式下，我们也无法连接到数据库。许多在线解决方案都让我们针对服务器运行 SQL 命令，但我们无法连接（即使在单用户模式下）到数据库来针对服务器运行命令。
具体错误信息：
无法打开数据库“模型”。它正在恢复中。（Microsoft SQL Server，错误：927）
SQL Server (MSSQLSERVER) 服务正在启动。 无法启动 SQL Server (MSSQLSERVER) 服务。
发生服务特定错误：1814。
我们需要尽快重新启动服务器并运行。
答案1
事实上，这个问题在一段时间前 (2012 年 7 月) 就已通过微软的付费支持电话得到解决。
由于这篇文章的浏览量如此之多，我决定添加我们与微软通话的“电子邮件摘要”的删节版。
发布的是一个 pastebin 的链接：http://pastebin.com/QqNz7xYd
希望这些信息能够帮助任何遇到此问题的人
Problem Description:
=============================
You Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) default instance on SERVERNAME server and you were trying to start the SQL SERVER service which was failing to start.
Analysis\Troubleshooting:
=============================
We checked the SQLS SERVER ERRORLOG
===========================
2012-06-20 19:33:41.48 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)
2012-06-20 19:33:41.50 Server (c) Microsoft Corporation.
2012-06-20 19:33:41.50 Server All rights reserved.
2012-06-20 19:33:41.50 Server Server process ID is 2596.
2012-06-20 19:33:41.51 Server System Manufacturer: 'HP', System Model: 'ProLiant DL360 G5'.
2012-06-20 19:33:41.51 Server Authentication mode is MIXED.
2012-06-20 19:33:41.51 Server Logging SQL Server messages in file 'D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2012-06-20 19:33:41.51 Server This instance of SQL Server last reported using a process ID of 2816 at 6/20/2012 6:10:15 PM (local) 6/20/2012 10:10:15 PM (UTC). This is an informational message only; no user action is required.
2012-06-20 19:33:41.51 Server Registry startup parameters:
-d D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-e D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l D:\Mssql\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2012-06-20 19:33:41.56 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-06-20 19:33:41.56 Server Detected 4 CPUs. This is an informational message; no user action is required.
2012-06-20 19:33:41.65 Server Using locked pages for buffer pool.
2012-06-20 19:33:41.88 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2012-06-20 19:33:42.33 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-06-20 19:33:42.49 spid7s Starting up database 'master'.
2012-06-20 19:33:42.86 spid7s Resource governor reconfiguration succeeded.
2012-06-20 19:33:42.86 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2012-06-20 19:33:42.87 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2012-06-20 19:33:42.87 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2012-06-20 19:33:43.02 spid7s SQL Trace ID 1 was started by login "sa".
2012-06-20 19:33:43.03 spid7s Starting up database 'mssqlsystemresource'.
2012-06-20 19:33:43.05 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
2012-06-20 19:33:43.24 spid10s Starting up database 'model'.
2012-06-20 19:33:43.24 spid7s Server name is 'SERVERNAME'. This is an informational message only. No user action is required.
2012-06-20 19:33:43.27 spid10s The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-06-20 19:33:43.28 spid10s Error: 927, Severity: 14, State: 2.
2012-06-20 19:33:43.28 spid10s Database 'model' cannot be opened. It is in the middle of a restore.
2012-06-20 19:33:43.36 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.
2012-06-20 19:33:43.36 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
===========================
From ERRORLOG we can see that SQL SERVER trying to bring model database online but since model database in recovery state hence we were unable to run database recovery.
We also know that whenever SQL SERVER start we recreate TEMPDB database by coping the content from model database.
Now since model database didn’t started hence TEMPDB startup also failed which is one of critical system database and this caused SQL SERVER service to failed
Solution:
=====================================
· To fix this issue we started SQL SERVER EXE with following parameter from command prompt
SQLSERVR.EXE -c -T3608
· Then after we detached model database using following T-SQL
sp_detach_db model
· Then after we attach the model database using following T-SQL
================================
USE MASTER
GO
CREATE DATABASE MODEL
ON (FILENAME = 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MODEL.MDF')
FOR ATTACH ;
================================
· After that SQL SERVER service came online successfully
答案2
您可以绕过自动恢复来启动 SQL 服务吗？
net start mssqlserver /f /m /t3608
然后尝试恢复模型？
sqlcmd -e
restore database
model go
编辑：找到此链接也更加深入，可能也更准确。