In SQL Server, the system databases are a set of databases that are created and maintained by SQL Server itself. These databases are essential for the functioning of the SQL Server instance. Here are the main system databases:
- master: This is the primary system database and contains all the system-level information for a SQL Server instance. This includes information about server configuration settings, login accounts, linked servers, and system stored procedures. The master database keeps track of all other databases and the location of their files, so it's critical for the operation of the SQL Server instance.
- model: This database serves as a template for all other databases created on the instance. Whenever a new database is created, SQL Server makes a copy of the model database to initialize the new database. It contains the default configurations and objects (like tables, views, stored procedures) that should be present in any new database.
- msdb: The msdb database is used by SQL Server Agent for scheduling alerts and jobs. It contains information about backups, database mail, service broker, and various other system-level tasks. It stores metadata related to automation and scheduling, such as SQL Server Agent jobs, alerts, and operators.
- tempdb: The tempdb is a temporary workspace. It's used to store temporary tables, work tables, temporary stored procedures, and other temporary storage needs generated by SQL Server. tempdb is recreated every time the SQL Server service starts, so its data is not persistent across SQL Server restarts.
- resource (mssqlsystemresource): This is a hidden, read-only database that contains all the system objects such as system stored procedures and functions that ship with SQL Server. The resource database makes upgrading to new versions of SQL Server easier because it separates system objects from user data. It's not directly accessible but is critical for the SQL Server operation.
- master: Ensures the SQL Server instance can start and operate correctly. Losing the master database would mean losing all the metadata about the instance.
- model: Ensures consistent database configurations across the instance.
- msdb: Manages automation, scheduling, and backup metadata, making administrative tasks easier.
- tempdb: Provides necessary temporary storage for operations, essential for performance.
- resource: Separates user and system data, streamlining upgrades and maintenance.
Given their critical roles, it’s essential to regularly back up the master, model, and msdb databases. The tempdb and resource databases do not require backups because tempdb is recreated on start and resource is read-only and can be restored from the SQL Server installation media.
Understanding these system databases and their roles helps in maintaining and troubleshooting SQL Server instances effectively.