SQL SERVER – Location of Resource Database in SQL Server Editions

SQL
10 Comments

While working on a project of database backup and recovery, I found out that my client was not aware of the resource database at all. The Resource database is a read-only database that contains all the system objects that are included with SQL Server. Let us learn more about the Location of the Resource.

SQL SERVER - Location of Resource Database in SQL Server Editions resources-800x222

The location of this database is at different places in the different versions of SQL Server.

In SQL Server 2008:

<drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\.
The Resource database cannot be moved.

SQL SERVER - Location of Resource Database in SQL Server Editions resourcedb
ResourceDB location in SQL Server 2008

In SQL Server 2005:

The same place where a master database is located.
The Resource database has to move with the master database.

You can run the following commands to know resourcedb version and last updated time.

SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
GO

SQL SERVER - Location of Resource Database in SQL Server Editions resourcedbver

Here are a few recent blog posts on the same topic which you may find interesting:

Let me know what you think of this blog post and if you want me to create a SQL in Sixty Seconds post for this video. If you have a similar script that you may find will be helpful to users, please do share it with me and I will post it on the blog with due credit to you. You can always reach out to me via Twitter here. I hope this blog post helps you to find the Location of resource.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL System Table
Previous Post
SQL SERVER – Several Readers Questions and Readers Answers
Next Post
SQL SERVER – Question – How to Convert Hex to Decimal

Related Posts

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.

    Reply
    • Paresh Prajapati
      February 1, 2010 2:35 pm

      How can we know that all version applied on all the instance?

      Reply
    • 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

      Reply
  • This is useful information. Thanks both of you for sharing the same.

    Reply
  • Paresh Prajapati
    February 1, 2010 2:36 pm

    Hi Pinal,

    Can you more elaborate , it can be use to apply only new service pack? or any more other use for that database.

    Reply
  • 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

    Reply
  • Keith Badeau
    May 10, 2011 6:05 pm

    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.

    Reply
  • Sachin Mahtole
    May 10, 2011 9:17 pm

    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.

    Reply
  • Vijay Mane(DBA)
    June 4, 2013 11:22 am

    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’)

    Reply
  • Hi,

    My databases files are stocke?d to a drive named G: and F: , and these drives doesnt exists on my computer. Why

    Reply

Leave a Reply