• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3721
  • Last Modified:

mssqlsystemresource.mdf file is missing

Our network admin applied a service pack for SQL Server 2008 on the db server.
Now the SQL server main service and agent service will not start.  Looked at the log file which indicates that mssqlsystemresource.mdf file cannot be opened.  I went and looked in the BINN folder and sure enough it's not there.

How do I recover this?

What to do?

Thx,

Eric
0
Eric3141
Asked:
Eric3141
  • 5
  • 2
2 Solutions
 
Eric3141Author Commented:
I also enabled the File Services role on the server itself then disabled it.  Don't know if this may have caused this or not.

From previous log files I see that the mssqlsystemresource.mdf must have been there before because sql server had started successfully before.
0
 
Randy DownsOWNERCommented:
Maybe this will help

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/4febbbb9-7cb4-4167-bfe7-194d60c7e5d7/

Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

    NET START MSSQL$SQLEXPRESS /f /T3608


Then start SQLCMD:


SQLCMD -S .\SQLEXPRESS


Then run these commands from the SQLCMD prompt:


1> ALTER DATABASE mssqlsystemresource
2> MODIFY FILE (name = 'data',
3> FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BINN\mssqlsystemresource.mdf')
4> go
The file "data" has been modified in the system catalog. The new path will be used the next time the database is started.
1> ALTER DATABASE mssqlsystemresource
2> MODIFY FILE (name = 'log',
3> FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BINN\mssqlsystemresource.ldf')
4> go
The file "log" has been modified in the system catalog. The new path will be used the next time the database is started.
1> exit


Exit SQLCMD and run


NET STOP MSSQL$SQLEXPRESS


Now start SQL Server from the SQL Configuration Manager in the regular way.
0
 
Eric3141Author Commented:
Number-1:  the mssqlsystemresource.mdf file is not on any of the drives -- I have searched and came up with nadda.  From above it looks like you're thinking the file is actually there but SQL Server is looking for it in the wrong location.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Randy DownsOWNERCommented:
Yeah I though maybe the path was wrong.

Are all the network resources available? Is it possible the file was on a network drive that is no longer attached?
0
 
Eric3141Author Commented:
I just did a repair on the SQL Server 2008 R2 installation and can now restart the services.
0
 
Eric3141Author Commented:
The repair re-created mssqlsystemresource.mdf & mssqlsystemresource.ldf files in the BINN folder.
0
 
Eric3141Author Commented:
I found the solution to the problem.  I posted it here so that it gets included in the knowledge base for others later.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now