SQL Server: Should We Move the Resource Database?
 Caution |
|---|
| Microsoft strongly suggests you NOT CHANGE location of resource database data and log files. This article is purely informational article describing how you would do it. This Article only shows way to move resource database because in Books online states you cannot move it, which is incorrect. EDIT: The Books online document is corrected now. And the Connect Item that was raised is closed now by Microsoft which means correct information is now in BOL. PLEASE DON'T CHANGE LOCATION OF RESOURCE DATABASE IT IS NOT SUPPORTED BY MICROSOFT If you choose to apply the method, it's fully at your own risk. Below is only for demonstration purpose, created to support the connect item feedback |
Scope
This article explains why you should not move Resource database and inconsistency in BOL definition that you cannot move resource database. You should not move it Microsoft does not recommend you do so.
**
JUST FOR DEMO**
On an experimental machine MSSQLSYSTEMRESOURCE database location is
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA
Inconsistency in Resource DB Online Content
Found inconsistency in Books Online(BOL) content for resource database. If you look at BOL Content about master database it says
" The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location."
This is not completely correct and reading above it seems one has to definitely move resource database to same location if master database is moved. You do not and should not move your resource database if you move master database.
Going further if you look at contents of Move System databases BOL. In 'Moving resource database section' it is written
"The location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\ The database cannot be moved."
Both information is contradicting and is creating confusion. Connect Item has been raised through which feedback has been given to Microsoft team.
In SQL Server 2005
In SQL Server 2005 users were allowed to copy the resource database off to another location. If you read Master Database content on BOL it says that
" The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location."
It was documented to move resource database so users started moving it and playing around with it by moving to the different location. This has repercussions though many users complained that after moving resource database when they tried applying a service pack or CU update it failed. This behavior is documented in This and This support article. When documents were updated for SQL server 2008 somehow content team forgot to remove the above-quoted paragraph and so was lying there
In SQL Server 2008 and Onwards
The failure caused in SQL Server 2005 during service pack upgrade which was eventually caused by movement of resource database location led to change in resource database location and it was decided that resource database would reside in the BINN folder along with the rest of the binaries. It's specifically in BINN folder because it does not contains any USER data only information related to SQL Server. Also, users would not touch files present in BINN folder
Moving the Location
Now we would try to move it to location
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data
For moving you have to start SQL Server services in single user mode and using trace flag T3608.
Go to CMD right click on it and select run as administrator this will open command prompt with admin privileges.
type
NET STOP MSSQLSERVER
If you have named instance type
NET STOP MSSQL$INSTANCENAME
This will stop SQL Server services. Now we have to start SQL Server in single user mode with trace flag T3608
type
NET START MSSQLSERVER /f /T3608
This will start SQL Server in single user mode see screenshot
Now type SQLCMD as shown in figure
Now you have to run below commands to change location of resource database to
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.mdf')
----
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mssqlsystemresource.ldf')
When you run above command you would see above . Please note after running each command you have to also type GO and press enter. Please also note message saying that file has been modified has to come if it does not movement is not proper.
After above is done successfully stop SQL Server service using
NET STOP MSSQLSERVER
Now go and move resource database mf and ldf files to location
D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
Now start SQL Server services from command prompt or from SQL Server configuration manager
You can see location has changed
Conclusion
No Microsoft does not recommends its users to change location of resource database. This article was solely created as demo article to point out inconsistency in BOL about resource database. The incorrect information has been corrected.