There are three types of internal objects: work files, work tables, and sort units. Internal objects are similar to user objects, except that they are not visible using catalog views or other tools to retrieve metadata. This means that entries concerning a temporary object are stored in the system catalog and you can retrieve information about it using the sys.objects catalog view.

All user objects stored in tempdb are treated by the system in the same way as any other database object. The other objects stored in this system database are table variables and table-valued functions. Private and global temporary tables, which are created by users, are stored in the tempdb database. The system administrator can change the properties of the model database to adapt it to the specific needs of their system. It contains the subset of all system tables of the master database, which every user-defined database needs. The model database is used as a template when user-defined databases are created. (The section “Backing Up the master Database” in Chapter “Backup, Recovery, and System Availability” explains when it is necessary to back up the master database.) model Database For this reason, you should back it up after the execution of such operations. Also, the master database is modified each time you perform an operation such as creating user databases or user tables. For example, the master database contains information about all other databases managed by the Database Engine, system connections to clients, and user authorizations.īecause of the importance of this system database, you should always keep a current backup copy of it. It comprises all system tables that are necessary for your work. The master database is the most important system database of the Database Engine. The following sections describe each of the system databases in turn. The content of this database is generally used for system upgrades. Note – There is another, “hidden” system database, called the resource database, which is used to store system objects, such as system stored procedures and functions.

Master database in sql server