Because I am a doofus

And, because, primarily, this is my repository of Useful SQL Information.

Master
Contains serverwide information about SQL

• logins
• linked server info
• configuration info
• info about user databases - locations of db files, key properties etc

Vital: SQL cannot start without it.

Resource

AKA mssqlsystemreources.mdf in the file system. Rather completely invisible in SSMS. Contains all the system objects deployed in SQL: so all the system stored procedures and system views (although these appear logically in the db to which they belong). It makes life easier when performing upgrades or applying Service Packs to have all this information in one place. Do not go looking for it. You will not find it except as a file.

Cannot be backed up.

Model

Template database: all databases need a base set of objects called the system catalog. (I know, American Spelling). When you create a new database, it uses the model database as the basis for its structure. So, if you want a people table in every single database you create, create it in the model database.

MSDB

Stores information for SQL Server Agent, Service Broker, Database Mail, Log Shipping etc. SQL Server Agent job information gets stored here i.e. job parameters, schedule, execution history. MSDB also stores backups and maintenance plan information, as well as information about the servers and tables involved in log shipping.

If you're backing up the database logs every 15 minutes or so, and you have lots of databases in Full Recovery Mode, you need to make sure that you have a regular tidyup of MSDB with a cleanup task, to make sure it doesn't get too big.

Distribution

Used during replication, it stores metadata and history information and transaction information for transactional replication. Until replication is set up, the distribution database is invisible in SSMS, however, the data files are installed by default.

TempDB

It's all in the name for this one: temporary data and data objects. Temporary Tables. Temporary Stored Procedures. That sort of malarkey. Also used for sort operations for large queries, some indexing, and during dbcc checkdb (database_name, repair_rebuild). Thus tends to get a bit out of control on occasion.

Gets recreated every single time SQL Server Service is started (very handy if it's gotten a little Out Of Control recently).

Cannot be backed up.