SQL SERVER - What is the role of resource database WITH example

Before run below script run the detail  of resource database.

 

·        The resource database is read only database which contain the all Sys.Objects is SQL Server.

·        Resource database physical location is <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. And the file name is always mssqlsystemresource.ldf.

·        This file cannot backup and restore. But if it required than you have to simple copy and paste option.

·        We cannot overwrite resource database.

·        Resource database can only modify by Microsoft customer support service specialist. Resource database ID is always 32767

·        To get the version number, last update date time and to access the sql server object definition of resource date base we can use  

SELECT SERVERPROPERTY('ResourceVersion');

GO

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

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

GO