For a large majority of SQL developers and BI analysts we will tend to access instances of SQL Server and go directly to the data to pull what we need. We log in, select our user database(s), write queries, and generate datasets from them. But what about those 4 visible databases that we see in our Object Explorer all the time? They just sit there and we don’t entirely know what they do or what we can do with them. Well, I’m going to spell it out for you.
master
Some of us have actually used this database before and understand the jist of what this database does and the information that it holds. To clarify, the master database is a system database that contains all of the system-level information for that instance of SQL Server. This means it contains all logins, linked servers, all endpoints, database information and objects, and other configuration settings will be stored here. This database must be present otherwise SQL Server will not start.
Pro Tip: Always make sure you take regular backups, otherwise any corruption of the master database will result in you having a giant headache and all of your changes will be lost in space somewhere
When maintaining and backing up this database always remember, as a rule of thumb, that you shouldn’t ever create user object in master. Otherwise it will need to be backed up much more often.
model
This database does exactly as the name describes; it is a model for any new and upcoming databases. The model database contains properties, users, stored procedures, tables, views, etc. and can be changed at any time without disturbing currently existing databases. Using this you can create your ideal model for every new database and pre-load it with users and stored procedures, making life ultimately easier.
This is the only function allowed to this database. It does nothing else.
msdb
If you are familiar with any DBA responsibilities or functions then you will be, at least, a little familiar with msdb. This is because msdb is used by the SQL Agent, database mail, Service Broker, log shipping, and other services. This also holds the backup history for the instance.
tempdb
Out of all of the databases on an instance, tempdb is probably the busiest. It is the background workspace that SQL Server uses for ERRYTHANG! This includes query processing and sorting, temporary tables, snapshot isolation, user-created temporary tables, table variables, etc.
SQL Server will always drop and re-create the tempdb every time the SQL Server service is started. Once the server restarts everything you once had will be gone.
Maintaining and keeping the tempdb properly sized is vital for the performance of your SQL Server instance. By default, tempdb starts at 8MB in size and will grow by 10% until the drive is full. This means that system resources will be spent during a large dataset resulting query just to be able to accommodate for it.
Resource
There is actually one more system database that exists, although you cannot see it. This is a hidden system database where system objects are stored. By normal methods you will not be able to see this; however, you are able to access the data files by navigating to C:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\Binn.
Typically, the only way to access the contents of this database is by using the OBJECT_DEFINITION system function.
That One SQL Blog 