Error: 945, Severity: 14, State: 2 | Database 'mssqlsystemresource' cannot be opened

This is a somewhat continuation post from my post Error: 5123, Severity: 16, State: 1 when moving TempDB.  So breaking my laptops SQL Instance and then fixing it got me interested in how else could I break SQL Server and make it not start up.  I recalled having seen a post where someone moved master and the resource database, and they couldn't get SQL Server to startup after doing so.  I couldn't find that specific thread on the forums, but I didn't really need it.  I can break a SQL Server without having to follow someone else's instructions, at least I have been pretty successful at doing so in the past while testing things on development servers.

To move the master database is fairly simple and doesn't require a whole lot of effort to do.  It is documented a few different places online in the Books Online so I won't go into specifics here.  One important item listed in the Moving System DatabasesBOL entry is:

The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the masterdatabase, you must also move the Resource database to the same location as the master data file.

This is where SQL Server 2000 DBA's can get themselves into trouble since we know how to move master, we sometimes assume that we know what to do and just jump right in and do what we know.  It is also one way to generate the error that is the title of this post.  To demonstrate this problem and how to fix it, I moved the master.mdf and master.ldf files on my laptop from c:\program files\microsoft sql server\mssql.2\mssql\data\ to c:\program files\microsoft sql server\mssql.2\mssql\ and used the SQL Server Configuration Manager to change the startup parameters to point to the new path for the master database.

Now if you read the BOL Entry, there is an additional step required to to change the path to the Resource database.  I intentionally left this step off to demonstrate how to fix the error should you make this mistake.  When I attempt to start SQL Server after the move, I get the following in the ErrorLog:

Starting up database 'master'.
Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
CHECKDB for database 'master' finished without errors on 2008-05-10 01:30:00.483 (local time). This is an informational message only; no user action is required.
SQL Trace ID 1 was started by login "sa".
Starting up database 'mssqlsystemresource'.
Error: 17207, Severity: 16, State: 1.
FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf'. Diagnose and correct the operating system error, and retry the operation.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf". Operating system error 2: "2(The system cannot find the file 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 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.ldf" may be incorrect.
Error: 945, Severity: 14, State: 2.
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

To fix this, we have to first drop to command prompt again, by running cmd in the Run box:

Then change directories to the Binn directory under your SQL Instances path:

Then run sqlservr with -f and trace flag 3608 as startup parameters which will start SQL Server in minimal configuration mode and prevent recovery of every database except master.  One note is that the -T3608 is case sensitive.

image


NOTE: Do not use any other startup parameters or Trace Flags as these can cause SQL to fail to start.


PS: WENN DER COMMAND SO NICHT GEHT (UND EINE FEHLERMELDUNG A LA "



our SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem" KOMMT, DANN BITTE NOCH DEN PARAMETER -sUNICORN21 oder wie die Instanz heißt angeben. Also sqlservr -sUNICORN21 -f -T3608


When you start SQL Server from the command prompt it will spool the log information out to the command prompt screen. When it shows Recovery is complete the SQL Server Instance is running in single user mode and can be connected to through SSMS or sqlcmd.

image


Once you connect you can then run the correct ALTER DATABASE scripts to fix the Resource Database path.


ALTER DATABASE mssqlsystemresource  
    MODIFY FILE (NAME=data, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource  
    MODIFY FILE(NAME=LOG, FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\mssqlsystemresource.ldf');
GO

Once this has been run, you can close the SQL Server Instance running in the command prompt by pressing Ctrl+C with the window active.  Then restart the SQL Service from the Services.msc snapin or the Computer Management Console and you should be back in business.

Stay tuned as I have a few more intentional crashes that I plan to demonstrate and fix in the next few weeks. 


Edit Note:  This should not be an issue in SQL Server 2008 since you can not move the Resource Database as per http://msdn.microsoft.com/en-us/library/ms143359.aspx. However, a reference entry in the BOL regarding what you can't do doesn't stop people from making this mistake.  In the case of SQL Server 2008, you would have to move the Resource database files back to the <Instance Path>\MSSQL\Binn folder where it belongs.

- Thanks to noeldr for pointing this out in comments.