Special System Databases

A new SQL Server 2000 installation automatically includes six databases: master, model, tempdb, pubs, Northwind, and msdb.

master

The master database is composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations, usage, systemwide configuration settings, login accounts, the existence of other databases, and the existence of other SQL servers (for distributed operations). The master database is absolutely critical to your system, so be sure to always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such activities, you should back up master.

model

The model database is simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database. If you'd like every new database to start out with certain objects or permissions, you can put them in model, and all new databases will inherit them.

tempdb

The temporary database, tempdb, is a workspace. SQL Server's tempdb database is unique among all other databases because it's re-created—not recovered—every time SQL Server is restarted. It's used for temporary tables explicitly created by users, for worktables to hold intermediate results created internally by SQL Server during query processing and sorting, and for the materialization of static cursors and the keys of keyset cursors. Operations within tempdb are logged so that transactions on temporary tables can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it. No recovery information is needed because every time SQL Server is started, tempdb is completely re-created; any previous user-created temporary objects (that is, all your tables and data) will be gone. Logging only enough information for rolling back transactions in tempdb was a new feature in SQL Server 7 and can potentially increase the performance of INSERT statements to make them up to four times faster than inserts in other (fully logged) databases.

All users have the privileges to create and use private and global temporary tables that reside in tempdb. (Private and global table names have # and ## prefixes, respectively, which I'll discuss in more detail in Chapter 6.) However, by default, users don't have the privileges to USE tempdb and then create a table there (unless the table name is prefaced with # or ##). But you can easily add such privileges to model, from which tempdb is copied every time SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you choose to add those privileges to the model database, you must remember to revoke them on any other new databases that you subsequently create if you don't want them to appear there as well.

pubs

The pubs database is a sample database used extensively by much of the SQL Server documentation and in this book. You can safely delete it if you like, but it consumes only 2 MB of space, so unless you're scrounging for a few more megabytes of disk space, I recommend leaving it. This database is admittedly fairly simple, but that's a feature, not a drawback. The pubs database provides good examples without a lot of peripheral issues to obscure the central points. Another nice feature of pubs is that it's available to everyone in the SQL Server community, which makes it easy to use to illustrate examples without requiring the audience to understand the underlying tables or install some new database to try out your examples. As you become more skilled with SQL Server, chances are you'll find yourself using this database in examples for developers or users.

You shouldn't worry about making modifications in the pubs database as you experiment with SQL Server features. You can completely rebuild the pubs database from scratch by running a script in the \Install subdirectory (located right under the SQL Server installation directory). In SQL Query Analyzer, open the file named Instpubs.sql and execute it. You do need to make sure that there are no current connections to pubs, because the current pubs database is dropped before the new one is created.

Northwind

The Northwind database is a sample database that was originally developed for use with Microsoft Access. Much of the documentation dealing with APIs uses Northwind, as do some of the newer examples in the SQL Server documentation. It's a bit more complex than pubs, and at almost 4 MB, slightly larger. I'll be using it in this book to illustrate some concepts that aren't easily demonstrated using pubs. As with pubs, you can safely delete Northwind if you like, although the disk space it takes up is extremely small compared to what you'll be using for your real data. I recommend leaving Northwind there.

The Northwind database can be rebuilt just like the pubs database, by running a script located in the \Install subdirectory. The file is called Instnwnd.sql.

msdb

The msdb database is used by the SQL Server Agent service, which performs scheduled activities such as backups and replication tasks. In general, other than performing backups and maintenance on this database, you should ignore msdb. (But you might take a peek at the backup history and other information kept there.) All the information in msdb is accessible from the SQL Server Enterprise Manager tools, so you usually don't need to access these tables directly. Think of the msdb tables as another form of system tables: just as you should never directly modify system tables, you shouldn't directly add data to or delete data from tables in msdb unless you really know what you're doing or are instructed to do so by a Microsoft SQL Server technical support engineer.