The System Databases: master, model, tempdb, msdb
There are four system databases in Microsoft SQL Server 2000: master, msdb, model, tempdbmaster
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.