Every once in a while I like to review the different system tables for those new to SQL Server. With the rise of code first in Entity Framework, more developers are getting involved with SQL Server databases without some of the basic understanding of tools available to them.
First you have the Master database. The master database is the catalog of all databases hosted by an instance of SQL Server. You need to backup this database, especially for a local, sandbox, instance. If you don’t then you’ll have a more difficult time getting your databases and user accounts working again.
The second database you need to be aware of is called Model. When you create a new database, it copies the Model database as a baseline. Anything you want to be in any database you create, create it in the Model database. When you create a new database, the object you create in Model will be there.
TempDb is a third database for you to manage. This is the database that has the greatest impact on your SQL Server performance. If possible place it on its own disk. If you are using a workstation, this is not likely. The biggest thing is to pre-allocate space to it so that it does not have to grow dynamically. You don’t want to wait for it to grow when it is performing queries.
TempDb is used by every database on that Sql Server instance. So, give it enough room to breathe, and keep an eye out for auto-grow events.
The last database is MSDB. This is a database used by Microsoft to track some activity, and to store schedules and actions for SQL Agent. If you are using these features, then backing up MSDB is a plus. Should the database become corrupted, you can restore it from a backup without losing your task configurations or history.
There you go, SQL Newbees. You can get quite a bit more information on these databases with a good Google search; but, this provides you with an overview as to what these databases are and why they exist.
Cheers,
Ben