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
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
Maybe this will help
http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/4febbbb9-7cb4-4167-bfe7-194d60c7e5d7/
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 \mssqlsyst emresource .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 \mssqlsyst emresource .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.
ASKER
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.
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?
Are all the network resources available? Is it possible the file was on a network drive that is no longer attached?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the solution to the problem. I posted it here so that it gets included in the knowledge base for others later.
ASKER
From previous log files I see that the mssqlsystemresource.mdf must have been there before because sql server had started successfully before.