SQL Server system database

    SQL Server database size between 1MB and 1 048 516TB
    System DB: master,model,tempdb and msdb. There is the 5th hide system db named resouce, actual name is mssqlsystemresouce.

    Master DB:
        Consist of system tables. These tables trace the system installation and the database created later. Though there are a gourp of tables to store metadata in every database, the information in master db has more information, including disk space, file space allocated and used, the configration, port and login account information in system level, the db info in current instance and other SQL Server information(for distribute db).

        Master DB is very important, so keep a backup once the configuration changed or new database created.

 

    Model DB:

        Model is just a template database.

        When a new database is created, SQL Server will copy model db as the new db basic. If u want the new db include some objects or privilage, u can put the properties in the model db by using comand "alter database", then all new db will own these properties by inherited from model db.

 

    tempdb DB:

        Tempdb db is used as a workspace. The special feature of tempdb is it is recreated instead of restored.

        The objects in tempdb include: the temporary tables user created explicitly, the work tables when SQL Server processing query and the mid-result when sorting, the snapshot isolate lever information and filling the cursor.

 

    Mssqlsystemresource DB:

        Mssqlsystemresource is a hidden database, usually called resource. The reason microsoft hide it is for quick and safe updated, if no one can see this database, ms can install Service Pack(SP) by simply replacing resource DB. One thing need to point out is resource db can not be seen using normal query methods, such as sys.database and sp_helpdb.

        All excutable system objects, such as system stored procedure and functions, are all stored in this system.

        How to see mssqlsystemresource db:

             a). The file is in the installation folder. Such as "C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/data", there are 2 files named "mssqlsystemresource.mdf" 38MB and "mssqlsystemresource.ldf" 0.5MB

             b). Copy the 2 files metioned in a) as new name, then excute the following statement.

                  CREATE DATABASE resource_copy ON ( NAME = data, FILENAME = 'C:/Program Files/..../Data/mssqlsystemresource_COPY.mdf'), ( NAME = log, FILENAME = 'C:/Program Files/..../Data/mssqlsystemresource_COPY.ldf')

                  FOR ATTACH;

 

    msdb DB:

        SQL Server Agent services uses msdb database for executing backup or duplicating plan, also Service Broker will use msdb db.

        Actually, msdb can be dropped before SQL Server 2005, after dropped, SQL Server is normal running, but can not maintain any history backup and can not define task, warning and work or create duplicate.