SQLSimon.com

The system databases are those databases that are used internally by SQL Server. Each has a different use:-

• master - contains the information relating to all the other databases on this instance
• model - a template used when creating a new database
• msdb - stores information about how the instance works e.g. SQL Agent jobs, database back-ups, log shipping information
• tempdb - holds temporary objects e.g. temporary tables and is created every time the instance is started
• distribution - created if the instance is configured for replication and contains history and metadata for snapshot, merge and transactional replication
• mssqlsystemresource - new in 2005, the resource database is hidden and cannot be moved. It holds all the system objects information

It should not be necessary to make changes to any of these databases directly. However, it is useful to have a basic understanding of the system databases. For instance, collation errors can occur when running queries against temporary tables if the collation for tempdb is different to that of the originating database. This is a particularly exasperating error to overcome as the collation can be set at the instance, database and table levels and are not easily changed. Another difficulty is when restoring a database from one server to another and having mis-matched logins. This is known as orphaned database users as the database users exist in the originating database, but the login does not exist in the master database. Simply creating the login does not rectify the situation as the SIDs between the two databases does not match.