The Resource database contains SQL Server system objects. I use version-specific guidance to locate its files and plan recovery.
A client preparing backup and recovery work did not know about the Resource database. It contains system objects that appear logically through system schemas in other databases.
Keep the older location examples historical
SQL Server 2008 placed the files under the instance Binn folder, as shown in the original Explorer image. SQL Server 2005 used the master-database location and required moving Resource files with master. Those are dated examples, so check the installed version’s supported procedure.
SELECT SERVERPROPERTY('ResourceVersion') AS resource_version,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS resource_last_update_time;
Inspect the installed version
Use current installation and version documentation to identify the supported files and recovery procedure. These properties report version and update information; they don’t return a physical file path.
Don’t edit or attach the Resource database to make a user change, and don’t move it independently. Keep its recovery requirements in the system-database plan.
Reference: Resource database reference.
Related reading
- Slow Running Query – SQL in Sixty Seconds #146
- Sleeping vs Suspended Process – SQL in Sixty Seconds #122
- Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
- SQL in Sixty Seconds
The Resource database is not an ordinary user database, it is part of the installed SQL Server system.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
10 Comments. Leave new
This is a great topic. I just would like to add, that some of the benefits of implementing the Resource database in 2005 – 2008 are that it is much easier to apply service packs and to revert changes to multiple instances. For an example, when a dba has to apply a service pack to multiple instances, they can just copy mssqlsystemresource.mdf and mssqlsystemresource.ldf to the target instances.
How can we know that all version applied on all the instance?
Hi Feodor,
Can you plese tell me more about this Topic
“For an example, when a dba has to apply a service pack to multiple instances, they can just copy mssqlsystemresource.mdf and mssqlsystemresource.ldf to the target instances
This is useful information. Thanks both of you for sharing the same.
Hi Pinal,
Can you more elaborate , it can be use to apply only new service pack? or any more other use for that database.
Hi Pinal,
In SQL Server 2008:
Resouce database will be under:
:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\
There is a typo in the location for your post
Thanks for the information, I didn’t even know there was a resource database. I always look forward to receiving your blog posts as I always learn something new.
Feodor stated that there is a benefit to having separate resource DB such as easier to apply sp. Could someone explain this in more detail? Thank you.
Hi,
Interestingly, I copied resource database and pasted in other directory. Later changed name of mdf & ldf as test111.
Later Attached database using name test111 & reasigning log/mdf file
after attaching, just check all DMV.
Interesting topic.
Persisting all the system objects in the resource database allows for rapid deployment of service packs
and upgrades to SQL Server 2008. When installing a service pack, the process is simply one of replacing
the resource database with a new version and executing whatever modifications are required to the
operating system objects. This dramatically reduces the amount of time it takes to update SQL Server.
Even though the resource database isn’t accessible during normal SQL Server operations, information
about the database can be retrieved using system functions and global variables. The following code
returns the build number of the resource database:
SELECT SERVERPROPERTY(’ResourceVersion’)
To return the date and time the resource database was last updated, the following code can be executed:
SELECT SERVERPROPERTY(’ResourceLastUpdateDateTime’)
Hi,
My databases files are stocke?d to a drive named G: and F: , and these drives doesnt exists on my computer. Why