As a SQL Server professional, you may have come across various interview questions related to system databases. One such question that often arises is about the hidden system database called “mssqlsystemresource”. In this blog post, we will delve into this topic and provide a detailed explanation.
Here are a few key facts about the mssqlsystemresource database:
- It is a hidden system database.
- The ID of this database is 32768.
- It stores the schema of system objects.
- It helps in faster patching as there is no need to ALTER system objects.
One common misconception about this database is that it can be used to rollback a service pack by simply replacing the old files. However, this is incorrect. Service pack rollback requires a backup and restore of the database.
Another myth is that the mssqlsystemresource database is just like any other binary file, such as an exe or DLL, needed for SQL Server to run. In case the files are damaged, you would need the same version of the file from another SQL instance. However, this is also incorrect. The mssqlsystemresource database is specifically designed to store the schema of system objects and cannot be treated as a regular binary file.
If you are curious to see the objects under this database, there is a little trick you can try:
- Stop the SQL Server service.
- Copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to a new path (the location of these files can be found in the earlier blog mentioned).
- Start the SQL Server service.
- Use the following command to attach the data and log files as a new user database:
USE [master]
GO
CREATE DATABASE [mssqlsystemresource_copy] ON ( FILENAME = N'E:\temp\mssqlsystemresource.mdf' ) , ( FILENAME = N'E:\temp\mssqlsystemresource.ldf' ) FOR ATTACH
GO
By following these steps, you will be able to see the objects in the mssqlsystemresource_copy database. However, note that there are no tables in this database, only views and procedures which are located in the sys schema. Generally, these objects are visible under system views in other databases.
It is important to emphasize that in practical scenarios, there is no need to perform these steps. The purpose of this exploration is to provide DBAs with a deeper understanding of what is happening under the hood in SQL Server.
Lastly, it is crucial to mention that changing or replacing the system ResourceDB in production environments is not advisable. This blog post should be seen as educational and for exploration purposes only.
We hope this article has given you more insight into the hidden system database in SQL Server. Stay tuned for more informative blog posts on SQL Server concepts and ideas!