When I blog, it tends to be due to me having done something at work and thinking it may be useful, primarily for me to make a note of what I did somewhere but also because this might help someone else.  A while back, I posted regarding moving system databases to another location.  It was a pretty simple and already well documented but there is another database, a secret one which some may not know about and I didn’t think about when moving system databases.  This database is called mssqlsystemresource.

This database can not be backed up like a visible system database or a user database but when moving system databases, you may need to consider this database for the move to be successful.

The mssqlsystemresource database has two main functions.  Firstly, it contains all the physical system objects that come with SQL Server.  When you query sys.objects from database1, for example or any other user database, the object exists physically in the resource database, but logically in your user database.  Secondly, and because of this, it makes upgrades a much simpler prospect since the physical system objects can just be overwritten in one, known place.

For a more detailed overview of this database, here’s a link to Microsoft Docs with more detail:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

Advertisements