System Databases in SQL Server 2005
System Databases: resource, distribution, master, model, tempdb, msdb
There are six system databases in Microsoft SQL Server 2005: master, msdb, model, tempdb, resource, and distribution. The distribution and resource databases are new system databases that were introduced for SQL Server 2005.
master Database
The master database records database-wide information for SQL Server 2005. This information includes user logins, configuration settings, user created databases and the physical location of files for those databases. SQL Server will not start unless you have a master database.
Do I need to back up the master database?
Yes, you must be regularly backing up your master database in SQL Server 2005. SQL Server needs this database for configuration settings and to be able to locate databases on the server.
msdb Database
The msdb database is used by SQL Server Agent for alerts and jobs, and also for Database Mail (among other things). Backup information is stored in the msdb database.
Do I need to back up the msdb database?
Absolutely. The msdb database must be put on a very regular schedule for backups in order to preserve all of your jobs and your backup information.
model Database
This database serves as a template whenever a new database is created in SQL Server 2005. The model database must always exist on a SQL Server 2005 database. If you modify a model database, all databases created going forward will inherit those modifications.
Do I need to back up the model database?
It is a probably a good idea to periodically back up the model database. The size isn’t all that large, and if you make changes, those changes will be preserved.
tempdb Database
The tempdb database stores temporary objects (like temporary tables, cursors, temporary sprocs). The database is available to all users. Every time SQL Server 2005 starts, a clean copy of this database is generated.
Do I need to back up the tempdb database?
No. You are actually prevented from performing backup or restore operations on tempdb by SQL Server 2005.
resource Database
The resource database is a read only database that contains all SQL Server 2005 system objects. There is no user data in the resource database that needs to be preserved.
Do I need to back up the resource database?
No. You are actually prevented from performing backup or restore operations on the resource database by SQL Server 2005.
distribution Database
The distribution database in SQL Server 2005 stores history for all types of replication and stores transactions for transactional replication. You may actually have multiple distribution databases in your setup (one database for each publisher).
Do I need to back up the resource database?
Yes, you should be backing up distribution databases if you are engaging in replication.