SQL Server 2000 System Databases

The System Databases: master, model, tempdb, msdb

There are four system databases in Microsoft SQL Server 2000: master, msdb, model, tempdb

master

In order for SQL Server 2000 to even start, you must have a functioning copy of the master database. The master database also keeps track of all other databases on the server, including the location of their files on the operating system. You should backup this database frequently on a scheduled basis. Nightly if at all possible.

These are some of the activities that will update the master database and require a master database backup:

  • Creating/Deleting a user database
  • Adding logins
  • Changing configuration on the server
  • Creating or removing backup devices

msdb

The msdb database keeps track of all jobs and backup activities. This database is essential for backup and recovery operations and should be backed up very frequently.

These are some of the activities that will update the msdb database and will require the msdb database to be backed up:

  • New or already occurring Backup Activities
  • New or already occurring Restore Activities
  • New jobs or scheduled tasks
  • Replication activities

model

The model database serves as a template for all new databases created on the server. You need only backup the model database when you make changes to it. It isn't a large database to back up, so it might be wise to just put it on a regular backup schedule and forget about it.

tempdb

The tempdb database is used for temporary storage in the database. It is recreated every time SQL Server is started and does not need to be backed up. It actually can't be backed up.