SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Identify the Location of Resource Database in SQL Server

In this article we will take a look at how to identify the location of Resource database in SQL Server using a TSQL script.

Importance of Resource Database in SQL Server

Microsoft initially introduced Resource database in SQL Server 2005. Resource database is a read-only system database which is hidden from users. System objects such as sys.objects are physically stored in Resource Database which logically appears in the sys schema of each database. However, resource database will only store system objects and you cannot store user data or metadata.

Resource database consists of two files namely mssqlsystemresource.mdf and mssqlsystemresource.ldf. By default these files are located in the <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Most importantly the ID of resource database is always 32767. The ID value of resource database has remained same across all versions of SQL Server 2005 and later.

Also Read How to Backup and Restore Resource Database in SQL Server

Identity the Location of Resource Database in SQL Server Using TSQL Script

Using the below TSQL Script one can easily identify the physical location of Resource Database in SQL Server.

Use master
GO

SELECT 'ResourceDB' AS 'Database Name'
    , NAME AS [Database File]
    , FILENAME AS [Database File Location] 
FROM sys.sysaltfiles 
    WHERE DBID = 32767
GO
TSQL Script to Identify ResouceDB Loaction in SQL Server

Current Version of Resource Database on my instance of SQL Server

Using the below script you can identify the current version of SQL Server. This version number is same as the build number of SQL Server. 

/* Version Number of Resource Database */

SELECT SERVERPROPERTY ('ResourceVersion') AS 'Resource DB Version Number';
GO
Resource DB Version Number

The build number 10.50.1600 means you are running the Release to Manufacturing (RTM) version of SQL Server 2008 R2.

Last Time when Resource Database was updated

Using the below script you can get to know when last time resource database was updated.

/* When Last Time Resource Database was last updated */

SELECT SERVERPROPERTY ('ResourceLastUpdateDateTime') AS 'Resource Database Last Updated on';
GO
Resource DB was last updated on

Conclusion

In this article you have seen how easily you can identify the location of Resource Database using TSQL code. As a best practice, we recommend you to note down the Physical Location, Version Number, and when last time resource database was last update as part of your Disaster Recovery Documentation.

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement