If you have been worked with SQL Server 2005 and SQL Server 2008, you must aware of the new system database called System Resource (aka Resource) database. It is a different type of system database when compared to other system databases in SQL Server such as Master, Msdb, Model and TempDB. All the system information pertaining to that specific instance is stored in the Resource database. When a service pack / hot fix is installed the resource database is updated.

There are some interesting points to be noted with respect to Resource Database.

• It cannot be backed up / restored  using general backup / restore procedures from inside SQL Server. It has to be manually backed up similar to a file backup on a windows server.
• It has to be placed in the same directory where Master Database was placed, so when moving the system databases, Master in particular, the Resource Database has to be moved on to that location manually to have SQL Server start.

The following image displays the Resource database (actually mssqlsystemresource database) along with other system databases in a SQL Server 2005 installation.

With this piece of information, when you look for the same database in a SQL Server 2008 installation, you would be surprised that it does not exist anymore with the other system databases. In SQL Server 2008 the location of the Resource database has been changed to the Binary directory Binn. Due to this, when the master database is moved to a different location, there is no additional tasks related to resource database in SQL Server 2008.

The picture below displays the location of the resource database in a SQL Server 2008 installation.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty