Recently I had a SQL Server 2008 R2 SP2 up gradation, after the installation of SQL Server 2008 R2 Sp2, the SQL Server service wouldn’t start. I had gone through the Windows Event Log, and I found an error
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
Work Around:
- I had gone through the mentioned directory and I didn’t see any mssqlsystemresource.mdf data file.
- I had confirmed that there is no such data file available in the drives.
- I had copied mssqlsystemresource.mdf and ldf file from another SQL Server from the below location
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Template
- Paste the mssqlsystemresource.mdf and ldf into the affected server in the mentioned location
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\
- Then I restart the SQL Server service and it worked fine.
But When I tried to login into server, I got an another error
Login failed for user SQLTEST\AgentService. Reason: Server is in script upgrade mode. Only administrator can connect at this time.[SQLState 42000]”
Work Around:
Manually start the SQL Server agent after you perform the upgrade.
Dustin Hughes
11 years agoI have generated an SQL Script from 2012 and set it so that it will generate with compatibility to SQL Server 2008. However it is a large sql file, around 700mb.
get smart
11 years agoI am an professional Oracle DBA having 5 exp in Database administration,and i want to add sql server DBA skills in my knowladge bucket. Please suggest me how to proceed and what could be the efficient and fruitfull way to learn this technology also.
Jim Tsinaforniotis
11 years agoVarun R thank you. Your solution work just fine on me.
Thanks again.
Varun R
11 years agoYou are welcome.