Special System Databases

A new SQL Server installation will automatically include several 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 that you always keep a current backup copy of it. Operations such as creating another database, changing configuration values, or modifying login accounts all make modifications to master , so after performing such activities, make sure you 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 . 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-creatednot recoveredevery 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 objects (that is, all your tables and data) will be gone. Logging only enough information for rolling back transactions in tempdb is 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 will be discussed 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 the SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you do choose to add those privileges to the model database, you must remember to revoke those privileges on any other new databases 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, although it consumes only 2 MB of space. So if you're not scrounging for a few more megabytes of disk space, we recommend leaving pubs there. This database is admittedly fairly simple, but that's a feature, not a bug. The pubs database provides good examples, without a lot of peripheral issues obscuring their central points. Another nice feature of pubs is that it's pretty ubiquitous in the SQL Server community, which makes it easy to use to illustrate examples without requiring the audience to try to understand the underlying tables. As you become more skilled with SQL Server, chances are you'll find yourself also using it to illustrate examples with your developer or user community.

Northwind

The Northwind database is another sample database that was originally developed for use with Microsoft Access. Much of the documentation dealing with application programming interfaces (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. We'll be using it 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 needed is still extremely small compared to what you'll be using for your real data. We recommend leaving Northwind there.

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.