SQL Server Default/System Databases

SQL Server :

SQL server is a relational model database server designed for client/server use. It is a computer application used to create desktop and maintains a set of system-level databases. The main purpose of the SQL Server is to store user database information to support applications. When SQL Server is installed it usually creates master, model, msdb, tempdb resource and distribution (last three depends on version of SQL Server) system database by default.

System Database:

It is a system intended to organize, store, and retrieve large amounts of data easily. System databases contain information about user databases as well as meta-data about SQL Server, SQL Server Agent, jobs, alerts, DTS packages and more.

During Installation of Microsoft SQL Server the databases that are automatically created on every SQL Server instance are described as under:

• Master Database
• MSDB Database
• Model Database
• TempDB Database
• Resource Database
• Distribution Database
• ReportServer
• ReportServerTempDB

MASTER DATABASE:

The Master database is the system database and it contains information about the running server's configuration. The master database also keeps track of all other databases on the server, including the location of their files on the operating system. The information which is captured in the Master database includes SQL Server instance level configurations, linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc. It is the only database which allows full backups. If the Master database is corrupted it should be restored from the latest backup, depending on whether SQL Server is able to start with a damaged Master database. Sometimes the Master database is so corrupted that SQL Server cannot start at all and cannot be restored; in that case, we can rebuild the server from the command prompt and restored from latest backup once again. It is the most important database of the SQL Server because if this database is damaged, it will be difficult to use any other database in SQL Server. The procedure used to rebuild the master database for a corrupted installation of SQL Server is: Run Setup.exe to rebuild, verify, and repair a SQL Server instance, and rebuild the system databases.

MSDB DATABASE:

The MSDB is a composite, non-identical sequence database built from a number of primary source databases. Sequences from the higher priority databases are preferentially retained. MSDB database is used to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators, so on. It is created using Simple Recovery Model. This database also stores SSIS packages created in SQL Server 2005 & 2008. In this Database it is very easy to determine when each database and file group was last backed up. The Data Transformation Services (DTS) Packages in SQL Server 2000 are also stored in MSDB Database. If your MSDB database becomes suspect then you have two choices, either restore it from a backup or recreate it.

MODEL DATABASE:

The Model Database serves as a snapshot of every new database you create. It is used as the template for all databases created on an instance of SQL Server, any objects in the model database are automatically copied to the new database. You can change the database properties like: - create users, stored procedures, tables, views, etc. and once these changes take place, they are applied to every new database. Some operations that are not performed by the Model database:

1. Adding files
2. Dropping the database.
3. Renaming the database
4. Participating in database mirroring.
5. Changing the database owner.

TempDB DATABASE :

The Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, sub queries, and aggregates with GROUP BY, ORDER BY, for cursors. It is created on the master database device; the default size of tempdb is 2 MB. It is a global resource that is available to all users connected to the instance of SQL Server. One thing that is interesting to note about tempdb is that it is re-created every time the SQL Server service is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, you cannot back up this system database.

RESOURCE DATABASE :

The Resource database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database. One important thing to be considered is not to put the Resource Database files in compressed or encrypted NTFS file system folders as it will hinder the performance and will also prevent upgrades. This system database is not displayed in the SQL Server Management Studio object explorer, so users may not be aware of it.

1. 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.
2. 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.

DISTRIBUTED DATABASE:

The Distributed database is created on the Distribution SQL Server when replication is configured. This database stores metadata and history data for all types of replication, and transactions for transactional replication. It supports the data for transaction replication between the publisher and subscriber(s) and basically consists of two physical files namely distribution.mdf (data file) and distribution_log.ldf (log file). It is basically created using Simple Recovery Model.

REPORTSERVER:

A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions and stores all the metadata and object related information which is used by reporting services. This database is created together and bound by name when a user installs SQL Server Reporting Service. We can reuse an existing report server database from previous installation or a different instance with another report server instance. The ReportServer database is created using Full Recovery Model. It's functionality includes:

1. Reports security
2. Job schedules and running jobs
3. Report notifications
4. Report execution history

REPORTSERVERTempDB:

Temporary storage for Reporting Services is the main purpose of ReportServerTempDB. It is created when a user installs SQL Server Reporting Service and basically used by ReportServer database to stores session information, cached reports, working tables used by reporting services etc. The ReportServer database is created using Simple Recovery Model.