For an instructor lead, in-depth look at learning SQL click below.
The SQL Server Resource Database is a hidden, read-only database that holds the system objects that are included with the SQL Server. This database makes the upgrading process more manageable by reducing the chances of encountering versioning issues. There’s a single database which hosts the definitions of system objects, a single point of reference that ensures discrete and consistent system object definitions across databases.
Accessing the Resource Database
While the Resource Database is hidden and you cannot connect to it directly, you can access and query the objects within it through certain system views. Here is an example of viewing definitions of the SQL Server’s system objects:
|
1 2 3 |
SELECT OBJECT_DEFINITION (OBJECT_ID('sys.objects')) AS ObjectDefinition; |
Back Up the Resource Database
Even though it’s not possible to back up the Resource Database like regular user databases, you can still protect its data by including the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files in your operating system’s backup plan. Here’s how you can identify the current location of these resource database files in SQL server:
|
1 2 3 |
SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255; |
Restore the Resource Database
If you need to restore your SQL server Resource Database, you will have to replace the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files with the files from your backup while SQL Server service is offline. Here’s one way to force SQL Server to run in a single-user mode:
|
1 2 3 4 |
net stop MSSQLSERVER "...\MSSQL\Binn\sqlservr.exe" -m |
Conclusion
Working with the SQL Server Resource Database is not a common task, but it’s a necessary one, especially for managing system objects and making the upgrading process more efficient. Remember to regularly back up your Resource Database files, and know how to restore them, ensuring that your system can recover from unexpected failures.