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.
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.
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
Here are a few recent blog posts on the same topic which you may find interesting:
- 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
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)