jump to navigation

Looking into the Resource database in SQL Server October 27, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags:
trackback

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
GO

In my case the output is

VersionOfResourceDatabase
—————————————————————————————————————————————————————————————————————————————————————-
11.00.2845

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
GO

Ιn my case the output is

ResourceDatabaseLastUpdated
—————————————————————————————————————————————————————————————————————————————————————-
2012-09-01 04:02:07.380

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
GO

The output is

CREATE VIEW sys.indexes AS
    SELECT i.id AS object_id,
        i.name AS name,
        i.indid AS index_id,
        i.type,
        n.name AS type_desc,
        sysconv(bit, i.status & 0×8) AS is_unique,            – IS_IND_UNIQUE
        isnull(ds.indepid, 1 – (i.status & 0×100)/0×100) AS dat

Hope it helps!!!

About these ads

Comments»

1. Dot Net Rules : Looking into the Resource database in SQL Server - October 27, 2012

[...] into the Resource Database.The Resource database is available since the SQL Server 2005 version. (read more) Share Posted: Σάββατο, 27 Οκτωβρίου 2012 1:37 μμ από το μέλος [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,786 other followers

%d bloggers like this: