Microsoft SQL Server System Databases

Microsoft SQL Server System Databases

In this post we will learn about Microsoft SQL Server System Databases. SQL Server includes the following system databases, every system database has its own importance and responsibilities.

System Databases:

master Database: Records all the system-level information for an instance of SQL Server.

msdb Database: Is used by SQL Server Agent for scheduling alerts and jobs.

model Database: Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Resource Database: Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

tempdb Database: Is a workspace for holding temporary objects or intermediate result sets.

Restrictions on system databases:

1. Changing collation. The default collation is the server collation.
2. Dropping the database.
3. Dropping the guest user from the database.
4. Enabling change data capture.
5. Participating in database mirroring.
6. Removing the primary filegroup, primary data file, or log file.
7. Renaming the database or primary filegroup.
8. Setting the database to OFFLINE.