1. Recently was encountered with a Developer saying the “sys” schema objects were no longer in the MASTER database . OR What is Resource Database?
There are following number of system databases in SQL server.
As the question is based on resource, am mainly focusing on resource DB here as follows.
Resource database is a read-only and hidden database. Resource DB physically contains all the system objects that accompanies with SQL Server. Logically all objects present in sys schema exist in each DB. The Resource database does not contain user data or user metadata.The Resource DB ID is always 32767.
By default you’ll find physical files for Resource database under the data directory :\Program Files\Microsoft SQL Server\MSSQL1.\Binn. There’ll be two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf.
How to take the backup and restore of Resource DB
SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
2. what is the use of RESOURCE Database inside SQL Server?
The Resource database appears to be a critical system database as the SQL Server service is now dependent on this.
(a) To allow for faster upgrades (upgrade is now a file copy instead of DROP and re-CREATE 1000s of system objects)
(b) To allow for rollbacks of QFEs (file copy semantics).
In SQL Server 2005 onwards service pack changes will be made to the Resource Database, which will indeed reflect the changes in all the system and user database of the particular instance.If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance .If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version.
3. Tell us one change in RESOURCE Database inside SQL Server 2008 R2 edition?
In SQL Server 2005, the data and log files for the Resource database depend on the location of the data file of the master database. Therefore, moving the master database also requires moving the Resource database to the same location. In SQL Server 2008, this dependency does not exist. The master database files can be moved without moving the Resource database.
In SQL Server 2008, the default location of the Resource database is :\Program Files\Microsoft SQL Server\MSSQL10_50.\Binn. The Resource database cannot be moved.
4. How can we find the properties of the RESOURCE database like version etc?
SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,