Thursday, September 29, 2011

Working with SQL Server System Databases

This post will brief about the SQL Server system databases.
SQL Server System Databses:
  •  master
The master database records all of the system level information for a SQL Server system. It records all login accounts and all system configuration settings. master is the database that records the existence of all other databases, including the location of the database files. master records the initialization information for SQL Server.
  • tempdb
The tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. It is used to hold the temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors and internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.
  • model
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
  • msdb
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

Restrictions on these databases:





Adding filegroups

Backing up or restoring the database

Changing collation. The default collation is the server collation

Changing the database owner (owned by dbo)

Creating a full-text catalog or full-text index

Creating triggers on system tables in the database

Creating a database snapshot

Dropping the database

Dropping the guest user from the database

Enabling change data capture

Participating in database mirroring

Removing the primary filegroup, primary data file, or log file

Renaming the database or primary filegroup



Setting the database to OFFLINE

Setting the database or primary filegroup to READ_ONLY

Creating procedures, views, or triggers using the WITH ENCRYPTION option

NOTE : The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.