Looking into the Resource database in SQL Server October 27, 2012Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: Resource database
1 comment so far
In this post I will be looking into the Resource database in SQL Server and what it contains and why it is very important for the normal operation of SQL Server.
I was talking with SQL Server developers the other day and they have never heard or looked into the Resource Database.
The Resource database is available since the SQL Server 2005 version.
I assume that you have installed a version of SQL Server 2008/2012 in your machine.
I also assume that you are familiar with the SQL Server system databases – master,model,tempdb,msdb.
Most people have not heard about the Resource database because it is sort of “secret”,”hidden”.Some people look to find it in the SSMS. It won’t appear there.
You cannot backup the Resource database.Each SQL Server instance has one Resource database.
It is a read-only database that contains all SQL Server system objects.The actual objects are stored in the Resource database.These objects appear logically in the sys schema of each database attached in our SQL Server instance.
Have a look at the picture below. I have logged into my local SQL Server 2012 Developer Edition and expanded the System Views node of the AdventureWorks2012 database.
The Resource database makes upgrades to new versions of SQL Server easier, faster more efficient.The actual name of the database is mssqlsystemresource.mdf. It is located in the C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn path. It is the same path that SQL Server was installed in my machine. If you want to back up this database simply copy and paste it somewhere else using the Windows Explorer.
If we want to find the version of the Resource database, in a new query window type,
SELECT SERVERPROPERTY(‘ResourceVersion’) AS VersionOfResourceDatabase
In my case the output is
As you see I am using the built in system function SERVERPROPERTY
If you want to find out when it was the last time the Resource database was updated you can type this bit of TSQL code in a query window in the SSMS.
SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ResourceDatabaseLastUpdated
Ιn my case the output is
If you want to get the SQL definition for the sys.indexes object, type the following TSQL code in a new query in SSMS
SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.indexes’))AS Sys_Indexes_Definition
The output is
CREATE VIEW sys.indexes AS
SELECT i.id AS object_id,
i.name AS name,
i.indid AS index_id,
n.name AS type_desc,
sysconv(bit, i.status & 0x8) AS is_unique, — IS_IND_UNIQUE
isnull(ds.indepid, 1 – (i.status & 0x100)/0x100) AS dat
Hope it helps!!!