Monday, June 13, 2011

Resource Database

This is the additional database introduced from sql server 2005. The main advantage of this DB is it makes upgradation much easier compared to earlier versions and this is the DB where all the system objects physically reside.  

We can’t find this DB in management studio but the mdf and ldf files (mssqlsystemresource.mdf, mssqlsystemresource.ldf) can be found in “<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\” folder.

These files should always be kept in the same location where master DB files are residing.

We can attach these files using command ‘sp_attach_db’ incase if we want the DB to be visible in Management studio.

This DB cannot be backed up/restored using normal ‘backup’/’restore’ command. We have to backup manually.

The DBID of this DB is always 32767.

No comments:

Post a Comment