SQL SERVER-System databases

System databases:

When we install Microsoft SQL Server, five system databases are automatically created for each SQL Server instance. These system databases allow the database engine and administrative applications to properly manage the system.

• master
• model
• msdb
• tempdb
• Resource (SQL Server 2005 and higher only)

1.  Master

• The master database manages the SQL Server instance. It stores system configuration, login details, etc.
• In SQL Server 2005 onwards, the Master database stores only the logical information in the system objects residing in the Sys schema.
• In the previous editions of SQL Server, the master database physically stores all the system objects (tables, Sprocs, etc.) information.
• One of the main things is that the master and resource DB must be located in the same directory. Otherwise, the server goes down.

2.  Resource

• Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).
• Prior to SQL Server 2005, the system-related data were stored in the master
• It’s a hidden and read-only database that is not accessible via SQL server management

3.  TempDB

• TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc.
• When the SQL Server instance is rebooted, the TempDB database is recreated every

4.  Model

• Model is a template database for all user-defined
• So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs, etc.), i.e., whenever you create a new database, the template will be taken from the model database.

5.  MSDB

• MSDB is the main database to manage the SQL Server Agent
• It provides some of the configurations for the SQL Server Agent service (such as jobs alerts, etc.), and it stores database backup details.

Path : C:\program files \MSSQL SERVER\MSSQL\BIN

File Name: mssqlsystemresource Select * From Sysdatabases Database ID:

Master: 1 Model: 3 TempDB:2 MSDB:4

Resourse: 32767.

How to set a database in a single user mode and multi-user mode:

1. Single-user mode: Only admins can access the database (users cannot access)
   • Alter database <database name> set single_user
   • Sp_dbooptions, ‘database name’, ‘single user’, ‘true’
2. Multi-User mode: All users can access the database                                                                                                Alter database <database name> set multi_user