SQL Server Error Msg:911 Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
The error on the subject line is not a good sign when you have the mammoth task of moving SQL Server system databases, I would agree that the task of system databases migration is not a day-to-day task and you must have practised the required steps.
For the sake of clarifications the complete error messages will be:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
No entry found with that name. Make sure that the name is entered correctly.
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mssqlsystemresource'.
No entry found with that name. Make sure that the name is entered correctly.
By default if you have followed the tasks as per the steps outlined in the BOL for moving system databases such as using ALTER DATABASE statement or attempt to start SQL services with trace flag -T3608, it will work for MASTER database and trouble may start with RESOURCE databases on following steps:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= D:\Data\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= D:Data\mssqlsystemresource.ldf');
GO
I would like to give extra information on resource databases which were introducted from 2005 version onwards, from BOL:
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 master database, you must also move the Resource database to the same location as the master data file.
You may have additional error messages/issues if the client tools & network protocols for SQL Server 2005 or 2008 version do not match with the source server, you will be presented with following error:
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
As per the error text you may think it is network related error due to the Login Timeout expired, in my experience I have seen this will occur if the network protocols such as TCP/IP or Named Pipes or Shared Memory are not enabled or do not match between source & destination servers within this databases migration task.
On the similar lines of this issue I have been presented with a question while I was in Tech-Ed India community lounge discussing user problems for solutions, so here is what I have outlined on the steps to move a resource database:
Step 1:
- Stop SQL server services
- Start SQL server Service in minimal/maintenance mode using the command line option -m or -f and include a trace flag -T3608 (this is used to skip recovery of any databases except Master database, so that resource database is not used).
- Note:In case Resource database is corrupt so server would not start, and copying over correct version (more detail later) of resource database files does not address the issue, possibly due to disk/media failure.
Step 2:
- Once the steps followed as per Step1 above, now change the location of data and log files as shown below:
ALTER DATABASE MSsqlsystemresource MODIFY FILE (NAME=data, FILENAME= ‘New_Location\mssqlsystemresource.mdf’)
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= ‘New_Location\mssqlsystemresource.ldf’)
Step 3:
- After the Step2 execution, the next step and the last step is to restart SQL Server services.
- This should complete the process by getting the SQL Server services enabling the Resource database files will now be used from the new location.
Few additional questions posed by that user are as follows, what to do when one of resource database is corrupt?
This is where the DBA tactics of backup strategy should work, you must COPY the files from the backups, the reason I have highlighted the files is resource databases are not like usual system or user databases and you cannot perform backup/restore using SQL Native methods. So the best practice is to perform FILE level backup for resource databases. Tip: you can perform file backup of the resource databases as and when system configuration changes have been performed such as hotfix or service pack installation or major changes to memory/cpu configuration on the relevant SQL Server instances.
To close down the topic here is the excellent reference by fellow MVP Jonathan Kehayias talking about Error: 945, Severity: 14, State: 2 | Database 'mssqlsystemresource' cannot be opened error and how to resolve it.