The difference between Master and Resource databases

The difference between Master and Resource database

Mster db is the mother database for all other databases like it consits of other database information, configurations, file locations, security context and etc. whereas Resource db is a database addition to master consists of database schema and stored procedures which requires to run databases in current instance. Resource db mainly introduced for Upgradation or rollback your installations in Sql server 2005.

Some differences:

Master :
1. Master will have data specific to your instance.

2. The database can be found in your sqlserver installation data folder with the name of master.mdf.

3. For any maintenance activity you need to make the db in single user mode.



Resource :
1. Resource will have schema and stored procedures to run your instance.

2. It is introduced in Sql server 2005 for upgradation purpose. In sql server 2000 if you want to upgrade to a new service pack, you would need to run many log scripts that drop and create system scripts. It is too long process and much time consume. In Sql server 2005 if you install service pack or quick fix a copy of resource database will overrite the old database. With help of this resource db you can easily upgrade or rollback your service packs.


3. The database can be found in your sqlserver installation data folder with the name of mssqlsystemresource.mdf.

4. It is a hidden database, you can't findout it in sysdatabases table. By using serverproperty we can view the information about the resource db.