SQL Server System Databases

 Many people does know about system databases but no one remembers detailed version and get confuse in tricky questions. So I have put this in points

Master Database :

1. 
   Master database is composed of system tables
2. It keep track of the server installation as a whole and all other databases that are subsequently created
3. Master database has system catalogs that keep information about below on the current instance

• disk space
• file allocations and usage
• system-wide configuration settings
• endpoints
• login accounts
• databases

4. The existence of other servers running SQL Server (for distributed operations)

  

Model Database:

             It is simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database. If you’d like every new database to start out with certain objects or permissions, you can put them in model, and all new databases inherit them. You can also change most properties of the model database by using the ALTER DATABASE command, and those property values then are used

by any new database you create.
    But all changes does not impact creation of tempdb. like no. of data and log file settings , initial size


Tempdb Database:

1. The tempdb database is used as a workspace
2. It is unique among SQL Server databases because  it’s re-created—not recovered—every time SQL Server is restarted. It’s used for temporary tables explicitly created by users
3. Its used for worktables that hold intermediate results created internally by SQL Server during query processing and sorting, for maintaining row versions used in snapshot
4. Because the tempdb database is re-created, any objects or permissions that you create in the database are lost the next time you start your SQL Server instance.
5. An alternative is to create the object in the model database, from which tempdb is copied. (Keep in mind that any objects that you create in the model database also are added to any new databases you
6. If you want objects to exist only in tempdb, you can create a startup stored procedure that creates the objects every time your SQL Server instance starts

MSDB Database:

 The msdb database is used by the SQL Server Agent service and other companion services, to perform scheduled activities such as

• Backups
• Replication tasks,
• Service Broker, which provides queuing and reliable messaging for SQL Server.

                 In addition to backups, objects in msdb support jobs, alerts, log shipping, policies, database mail, and recovery of damaged pages. When you are not actively performing these activities on this database,you can generally ignore msdb.
                              Prior to SQL Server 2005, it was actually possible to drop the msdb database, your SQL Server instance was still usable, but you couldn’t maintain any backup history, and you weren’t able to defi ne tasks, alerts, or jobs or set up replication. There is an undocumented tracefl ag that allows you to drop the msdb database,

Resource Database:

1. The mssqlsystemresource database is a hidden database.
2. It is usually referred to as the resource database.
3. Executable system objects, such as system stored procedures and functions, are stored here.
4. Microsoft created this database to allow very fast and safe upgrades.
5. you can upgrade to a new service pack that introduces new system objects by simply replacing the resource database with a new one. you can’t see this database using any of the normal means for viewing databases, such as selecting from sys.databases or executing
6. sp_helpdb. It also won’t show up in the system databases tree in the Object Explorer pane of
7. SQL Server Management Studio, and it does not appear in the drop-down list of databases
8. accessible from your query windows.
9. You can see the files for this db in your default binn directory "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn". 
10. We can copy data and log file of resource database and attach it as normal database also.