12 things to know about MSDB

MSDB is one the System Databases in SQL Server. You might be aware that System Databases are important for the normal functioning of SQL Server. Here is a list of 12 areas in which  MSDB is used in a SQL Server deployment. 

  • SQL Server Agent
  • Backup and Restores
  • Maintenance Plans
  • Performance Data Collector
  • Log Shipping
  • Utility Center Point
  • Database Mail 
  • Database Mirroring
  • Replication
  • Policy Based Management
  • SQL Browser
  • DTS and SSIS Package Store

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

Resource Database in SQL Server 2008

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