Guide to System Databases
Published: 3rd May 2013
There are 4 main system databases and 1 additional database. These are:
  • master
  • model
  • msdb
  • tempDB
  • distribution


Master



The Master database is exactly as it sounds... it is the master copy of everything SQL Server needs in order to function. Without the master database (or with a corrupt one), your SQL Server instance will not start up or allow access.

This database contains tables listing all databases (including file locations), collations, linked servers, users, configuration, and security. It also holds the encryption keys and certificates that are used to keep sensitive data safe.

How to Move the Master Database in SQL Server 2008 R2

Model



The Model database is the template for which any new database on that instance is created. If you set the Model to Simple recovery, then by default all new databases are set to Simple. If you have an initial size of 50MB then all new databases have a size of 50MB etc. It is literally a template.

The key thing to note is that although it is just a template and therefore seems unecessary unless you're planning on creating new databases, it would be prudent to note that the tempDB database is dropped and re-created every time SQL Server shuts down and restarts, therefore Model has to exist as it is also used as a partial template for tempDB.

How to Move the Model & MSDB Databases in SQL Server 2008 R2 (and SQL Server 2005)

MSDB



The MSDB database holds all the information about the SQL Server Agent, Database Mail, and Service Broker. It is used by the SQL Server Agent to log and maintain all scheduled tasks, alerts, and operators, it also logs all backups, who took them, where they are stored, and what times they were taken. It is there integral to the smooth running of a SQL Server instance. It should also always be backed up (as should Master) so as not to lose all your jobs and scheduled tasks.

How to Move the Model & MSDB Databases in SQL Server 2008 R2 (and SQL Server 2005)

tempDB



This is a disposable database that is dropped each time SQL Server closes down and re-created from scratch each time SQL Server starts up. Therefore it lives up to its name... it is temporary.

It's main function is to act as a sandbox for people to place temporary sets of data as a part of large queries. It can also be used to sort indexes during rebuilds and sort results sets for queries (that otherwise may not even use tempDB and therefore people don't realise it's still being touched), and for row versioning if you are running under certain database isolation levels.

How to Move the tempDB Database in SQL Server 2008 R2

Distribution



Distribution is a special system database as it is only created if the server is set up as a distributor within replication.

The database is used to track all data inserts, deletes, and updates which are part of replication and make sure they are delivered to the correct subscriber. It also stores replication histories and versions so that replications can be rebuilt if necessary without having to create whole new snapshots and re-initialising the subscriptions.

Comments:
NB: Comments will only appear once they have been moderated.