Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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


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


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)


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)


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

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.