Database Management Basics in SQL Server

I will try to give you information about system databases and user databases in a series named “Basics of database management in SQL Server”.

Databases in SQL Server

Basically, SQL Server has the following two types of databases:

1. System Databases.

2. User Databases.

System Databases

System databases are one of the databases created when SQL Server is installed. These databases are used for various operational and management activities for SQL Server.

Types of System Databases

There are four system databases named master, msdb, tempdb and model which can be seen on the SQL Server side. In addition, there is a system database named resource which is hidden and read-only. Now let’s examine each system database.

  • Master Database

    • Stores all system-level information for a SQL Server.

    • The dbid (database ID) of the master database is one (1).

    • The master database has a Simple Recovery Model.

    • It is a critical database and needs to be backed up.

    • SQL Server cannot be started without a master database.

    • Master and mastlog are the logical file names of the master database. Master.mdf (data file) and mastlog.ldf are the physical files of the master database.

You can use the following query to see the physical file location of the master database.

Some queries that can be used for the master database;

  1. Configuring the server using the master database can be queried with the following query.

  • The master database contains information about all other databases and their locations in SQL Server. We can see this information by running the following query.

  • The master database contains the login information in SQL Server. We can see this information by running the following query

  • The master database also contains information about users in SQL Server. The query to see user details is as follows:

Model Database

• The model database serves as a template database that is used to create new databases.

• The dbid of the model database is 3.

• By default, the recovery model of the model database is FULL.

• The model database should be backed up.

• Modeldev and modellog are the logical file names of the model database.

• Model.mdf (data file) and modellog.ldf are the physical files of the model database.

• You can use the following query to see the physical file location of the model database.

MSDB Database

• The MSDB database stores information about backups, SQL Server Agent information, SQL Server Jobs, alerts, and so on.

• The Ddb of the msdb database is 4.

• The recovery model of an msdb database is SIMPLE.

• We can back up an msdb database.

• MSDBData and MSDBLog are the logical file names of an msdb database.

• MSDBData.mdf (data file) and MSDBLog.ldf are the physical files of an msdb database. You can use the following query to see the physical file location of the msdb database.

TempDB Database

• Stores temporary objects, such as temporary tables, temporary Stored Procedures, and temporary tables for storing sorting and so on.

• The dbid value of the temp database is 2.

• The recovery model of a temporary database is SIMPLE.

• We cannot back up a tempdb.

• Tempdev and templog are the logical file names of tempdb.

• Tempdb.mdf (data file) and templog.ldf are the physical files of a tempdb. You can use the following query to see the physical file location of the tempdb database.

Resource Database

• This is a read-only database and is hidden from the user. Contains all system objects that are included in SQL Server.

• The Dbid of the Resource database is 32767 .

• The Resource database helps when you perform a SQL Server upgrade.

• You can see that sql server is installed as the physical file location in the directory with the names mssqlsystemresource.mdf and mssqlsystemresource.ldf in the bin folder. With the following query, index information can be accessed.

User Databases are databases created by users on their names.

I hope it has been a useful article.