June 5, 2011 at 11:04 pm
October 29, 2014 at 2:15 am
I needed a little bit more detailed information on how to solve this problem, however I am very Thankful to some others, namely the detailed input from: Pudge_DC (article below)
These are the errors from the Application Log in the Event Viewer for the following four files:
FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\model.mdf for file number 1. OS error: 21(The device is not ready.).
FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 21(The device is not ready.).
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
I tried the 'Rebuild System Databases' (http://msdn.microsoft.com/en-us/library/dd207003.aspx), did not work.
I tried the steps in Pudge_DC post, but could not get them to work, However I had another working SQL Server 2008 R2 install, so I performed the following steps:
1. On the non-working SQL Server 2008 R2 install (The one generating the errors in the Application log) > I opened the ..\MSSQL\DATA folder > copied the master.mdf and mastlog.ldf files
2. On a working SQL Server install > I Stopped the SQL Server Service > opened the ..\MSSQL\DATA folder and moved the existing master.mdf and mastlog.ldf files into the ..\MSSQL\Backup folder.
3. Then I copied the master.mdf and mastlog.ldf files from the non-working SQL Server into ..\MSSQL\DATA folder on the working SQL Server.
4. Now I was able to follow the steps posted by Pudge_DC, which are:
=====
Started the Server via Command Line with: (Where SQLEXPRESS is the name of your SQL server)
NET START MSSQL$SQLEXPRESS /f /T3608
Logged into SQL with: (Where SQLEXPRESS is the name of your SQL server)
SQLCMD -S .\SQLEXPRESS
Checked my current settings with
SELECT database_id, name, physical_name FROM sys.master_files (NOTE: sure enough the four files named above were referencing the E:\ drive)
GO
Then updated all the file names with this: (where FULL FILEPATH is the file path for each file)
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'modeldev'
GO
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'modellog'
GO
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'MSDBData'
GO
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'MSDBLog'
GO
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'tempdev'
GO
USE master
GO
UPDATE sys.master_files
SET physical_name = 'FULL FILEPATH'
WHERE name = 'templog'
GO
=====
5. Stopped the SQL Server Service > copied the modified master.mdf and mastlog.ldf files back over into the ..\MSSQL\DATA folder on the non-working (Original) SQL Server.
6. Started the SQL Server Service normally and it started correctly.
On the working (Secondary) SQL Server, move your files back from the ..\MSSQL\Backup folder back to the ..\MSSQL\DATA folder and start the SQL Server Service back up, to wrap things up!
Thank God and thanks to all those that contributed to this solution, God Bless!
October 29, 2014 at 2:42 am
Just a comment on this...
NEVER update the system tables. You can't touch them in most circumstances, it's very easy to make mistakes and break things and, by updating the system tables you've flagged that instance as one that will not be supported if you call Product Support.
The correct way to change the physical name for files in the system catalog is ALTER DATABASE <database name> MODIFY FILE (name = '<file's logical name>', physical_name = 'new path and file name') and to do that for each file that needs changing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply