SQL Server System Databases

SQL Server System Databases

SQL Server maintains a set of system-level databases, system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update. The system databases that you must always back up include msdb, master, and model.

SQL Server System Databases

Master Database: The main job of master.mdf file is to keep a track of location for various database files, system configuration, information about existing DBs, Servers that are connected together, or the endpoints.

Model Database: The model.mdf gives a template for creating user-database. When a CREATE DATABASE statement is passed, model database of is copied from previous DB to first part of new DB (the remaining database has void pages).

MSDB Database: The main task of msdb.mdf file is to schedule jobs for backup and restore history. For example: Name of the party that created online backup, at what time it was restored etc. Moreover, this primary data file is used by other applications like SQL Server Management Studio for task like restoring backup and replaying transaction logs.

TEMP Database: Temporary operations that performed while an instance is running, for example deleting, modification are performed by temp.mdf file. It is also used for objects like table variables, indexing etc (indicates that no object get saved permanently).

Resource Database: This database is hidden in SQL Server Management Studio and presents read-only version of system objects that exist in sys.objects of database.

Distribution Database: A system database that is stored on the Distributor server. The distribution database does not contain any user tables; it is used by the replication components of SQL Server, to store data including transactions, snapshot jobs, synchronization status, and replication history information. See the replication section for details. This database is also hidden in SQL Server Management Studio.

ReportServer Database : This is the Primary database for Reporting Services to store the Meta data and object definitions. A Reporting Services application uses two SQL Server relational databases for internal storage. By default, the databases have names as ReportServer and ReportServerTempdb. ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports. This database is also hidden in SQL Server Management Studio.

Click here to see how to Rebuild System Databases in SQL Server 2008.

<<Click here to see all posts>>

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.