SQL Server System Databases

SQL Server 2008/2005 contains five system databases.

Master, Model, Msdb, tempdb  and Mssqlsystemresource (aka resource). Other than this ReportServer and ReportServerTempDB we can consider as system databases if reporting services installed. You may count distributor as system database if replication configure.

Master	The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. The first database in the SQL Server startup process, If the master database is not present, SQL Server cannot start. needs to reside in the same directory as the Resource database Always take regular backups of the master database. Good Practice:  Do not create users objects in the master. Otherwise, master must be backed up more frequently. Normally when you open the SQL server and firing any query without proper USE statement it will create all object in default master database so keep practicing of always uses USE statement at top of any queries.
MSDB	The msdb database is used by SQL Server Agent for scheduling alerts and jobs, Operators, Alerts and by other features such as Service Broker and Database Mail. MSDB database is used to store the information related to the database backups and restore information. You need to make sure of purging old back up history from the msdb.
Model	The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. You can change most database properties, create users, stored procedures, tables, views, etc. – whatever you do will be applied to any new databases. The database configurations such as the recovery model for the Model database are applied to future user defined databases. Outside of its role as a template, model doesn’t do anything else.
Tempdb	Is a workspace for holding temporary objects or intermediate result sets. Purpose of temporary database to store temporary tables, table variables, cursors, create or rebuilding indexes sorted in Tempdb etc. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database, so permanent objects cannot be crated in tempdb database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb. The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with auto growing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of tempdb. Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.
Resource	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. When a service pack / hot fix are installed the resource database is updated. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file. SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups.  Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version. Read-only database that is not accessible via the SQL Server 2005 tool set The database ID for the Resource database is 32767 The Resource database does not have an entry in master.sys.databases To determine the version number of the Resource database, use: Copy SELECT SERVERPROPERTY('ResourceVersion'); GO To determine when the Resource database was last updated, use: Copy SELECT SERVERPROPERTY('ResourceLastUpdateDateTime'); GO