Little info about Resouce DB (SQL Server 2008-2005)

Little info about System Resource Database in (SQL Server 2008/2005)

The fifth system database in both versions (SQL Server 2008 – 2005) is Resource Database. In this DB we have all relevant information about systems objects and functions that we use every day in our daily routine, such as:

SELECT SERVERPROPERTY('ResourceVersion');
GO

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
GO

SELECT SERVERPROPERTY('Edition');
GO

The Resource Database is the read-only DB , all sys.objects in SQL Server are organized in this DB physically and logically are appears in sys schema of every DB.

Physical name for the Resource Database are mssqlsystemresource.mdf – data file and mssqlsystemresource.ldf – log file, the location has been changed and it is different in both versions.

SQL Server 2008
SystemDrive:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\.

SQL Server 2005
SystemDrive:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

The Resource DB depends from master DB, so if you remove your master DB also you will move the Resource DB.

SQL Server cannot backup up the Resource DB, it can be done manually and the restore should be done manually as well, and must be careful to not overwrite the current Resource DB.

More and other related info about Resource DB can be checked from here.

Advertisements

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