Hungry DBA .com for DBAs who like food
Default databases

SQL Server creates a number of default databases during an installation, which are described below.

Master - records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. Master is the database that records the existence of all other databases, including the location of the database files. Master records the initialisation information for SQL Server; always have a recent backup of master available.

Model - is a template for the creation of any new databases. When a database is created with all the defaults, it inherits all the same properties of the model database including: size, database options, and users within.

Msdb - SQL Server Agent uses the msdb database for scheduling alerts and jobs, and recording operations.

Tempdb – holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. Tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored here. Tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another. Place the tempdb on a fast I/O subsystem to ensure good performance. Stripe the tempdb database across multiple disks for better performance. Use filegroups to place the tempdb database on disks different from those used by user databases.

Northwind and Pubs – These are user databases that are installed by default for demonstration purposes. They can be removed, however are so small that they should not cause a problem in regards to resources and are useful for test purposes.