sql-server - FCB::打开失败:无法打开文件

Question

机器在我收缩数据库时重新启动，此后 SQL Server (MSSQLSERVER) 没有启动。阅读博客后，我将模板文件夹中的 master.mdf 和 maSTLog.ldf 文件替换为相同的文件，假设主数据库在我缩小时已损坏。

但是问题仍然存在，错误日志文件给出以下消息，

Starting up database 'msdb'.
Starting up database 'mssqlsystemresource'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
Starting up database 'model'.

我可以明确地说我的机器上没有E:\分区。你能帮我修复我现在认为是错误的路径吗？

Answer 1

我不知道这是一个错误还是 SQL Server 2014 通常会出现的问题。一些对数据库的引用，将保持这样:

E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf

所以您基本上必须直接在数据库中更改它们。

以管理员身份运行 CMD 控制台并访问您的数据库，如下所示:

SQLCMD -S .\

如果你有一个实例，或者像这样:

SQLCMD –S .\INSTANCENAME

用这些语句将文件重命名到默认位置:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\model.mdf');
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.mdf');
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\temp.ldf');
GO

之后，你的 sa 用户将保持没有密码(我遇到过，不确定是否总是发生)，只需再次登录到控制台并手动更改密码:

ALTER LOGIN sa WITH PASSWORD = 'newpassword' UNLOCK

当我执行最后一步时，我遇到了一个问题，因为我的默认登录配置了 Windows 身份验证，所以说明:

SQLCMD -S .\

没有用，只是添加没有密码的sa用户:

SQLCMD -S .\ -Usa

希望对您有所帮助。

我在这里找到了解决方案:

https://social.technet.microsoft.com/wiki/contents/articles/31786.sql-server-not-starting-after-fresh-installation.aspx