MSDB Database is one of the important system database.
The MSDB database serves SQL server Agent by handling jobs. It also supports other features such as Service Broker and Database Mail. SQL Server automatically maintains the history of Backup and Restore within MSDB Tables. It includes information like the name of the user that performed the backup. It also includes the time of the backup. It details the device or path where the backup is stored.
Backup events for all databases are recorded even if they were created with custom application or third party tools.
Because of all these reasons Microsoft always recommends to keep the MSDB files in fault tolerant storage space.
By default, recovery model of this database is set to Simple.
Roles:
MSDB is the only system database that has pre-defined database roles besides the regular fixed roles.
Below are the database roles and their use.
| Group Name | Database Role Name | Remarks |
| Database Mail | DatabaseMailUserRole | Only members of this database role can send mails through SQL Server. |
| Integration Services Roles | db_ssisadmin db_ssisltduser db_ssisoperator | Only members of this database role can work with SSIS packages. |
| Data Collector | dc_operator dc_admin dc_proxy | Used to implement the Data Collector Security. |
| Policy-Based Management | PolicyAdministratorRole | Only members of this database role can manage a SQL Server instance Policies. |
| Server Group | ServerGroupAdministratorRole ServerGroupReaderRole | Only members of these database roles can administer and use registered server groups. |
| SQL Server Agent Fixed Database Roles | SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole | Only members of one of these database roles can use SQL Server Agent. |
| Multiserver Environment | TargetServersRole | Used to work with a Multiserver Environment. |
| Server Utility | UtilityCMRReader UtilityIMRWriter UtilityIMRReader | Used to work with the Server Utility. |
Restrictions
We have many restrictions while working with this database. We cannot perform below activities on this database.
- We cannot drop MSDB database.
- We cannot drop the “Guest” user from this database.
- As it is one of the system databases, we cannot enable Change Data Capture (CDC) on this database.
- This database cannot participate in the database mirroring solution.
- We cannot remove the primary file group, primary data file or log file from the this database.
- Renaming of the database or primary file group is not possible for this database.
- MSDB database’s default collation is the MSSQL instance collation and cannot be changed.
- We cannot take MSDB database offline.
- We cannot change the primary file group to READ_ONLY.
2 Comments »