Basics of Database Administration in SQL Server: Part 1

Yashwant Vishwakarma
Jun 04, 2019

308.6k
0
7
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

Databases in SQL Server

There are basically the following two types of databases in SQL Server:

System Databases.
User Databases.

System Databases in SQL Server

System databases are databases that are 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 basically four system databases in SQL Server, master, msdb, tempdb and model that we can see. Apart from that, there is one more system database resource database that is hidden and read-only. Let's move ahead with each system database.

Figure 1: System Database

Figure 2: System Database

Master Database in SQL Server

All the system-level information for a SQL Server record by the master database.
The dbid (database id ) of master is 1.
The master database has SIMPLE RECOVERY MODEL.
It is a very important database and we must have the backup.
Without the master database the server can't be started.

Assume the master database files are missing or inaccessible, will SQL Server start or up?

Answer: No, SQL Server will not start because the master database is the important database and all the configuration and information needed to start the SQL Server is stored in the master database itself hence without the master database SQL server will not start.

Master database contains information about the server configuration. We can see the server configuration with the following query:
select * from sys.sysconfigures;
Master database contains information about all other databases and their location on SQL Server. We can see the information with executing the following query:
select * from sys.sysdatabases; or sp_helpdb
Master database contains information about logins in SQL Server. The following is the query by which we can see it:
select * from sys.syslogins;
Master database also contains information about users on SQL Server. The following is the query to see user details:
select * from sys.sysusers;
Master and mastlog are the logical file names of master database.
master.mdf ( data file ) and mastlog.ldf are the physical files of master database.

Query to see the physical file location of the master database:

SELECT name, physical_name FROM sys.database_files;

Figure 3: Master Database

For more information on the master database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

Model Database in SQL Server

The Model database acts as a template database used in creation of new databases.
The dbid of the model database is 3.
By default the model database has FULL RECOVERY MODEL.

Figure 4: Recovery Model

We can take a backup of the model database.
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.
The same query can be use to see the physical file location of the model database:

SELECT name, physical_name FROM sys.database_files;

Figure 5: Data Table

A user has created a new database. What will be the recovery model of that database?

Answer: Because the model database acts as a template database, when a user creates a new database it will inherit the property of the model database and as we know by default the recovery model of the model database is FULL (until or unless the user changes it), hence the new database is created by the user with FULL RECOVERY MODEL.

For more information on the model database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/model-database?view=sql-server-2017

MSDB Database in SQL Server

A MSDB database stores information related to backups, SQL Server Agent information, SQL Server Jobs, alerts and so on.
The Dbid of the msdb database is 4.
The recovery model of a msdb database is SIMPLE.
We can take backup of a msdb database.
MSDBData and MSDBLog are the logical file names of a msdb database.
MSDBData.mdf (data file) and MSDBLog.ldf are the physical files of a msdb database.
The same query can be used to see the physical file location of a msdb database:

SELECT name, physical_name FROM sys.database_files;

Figure 6: MSDB database Table

For more information on the msdb database use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2017

TempDB in SQL Server

It stores temporary objects, like temporary tables, temporary Stored Procedures and temporary tables to store sorting and so on.
The dbid of a temp database is 2.
The recovery model of a temp database is SIMPLE.
We can't take a backup of 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.
Same query can be use to see the physical file location of tempdb:

SELECT name, physical_name FROM sys.database_files;

Figure 7: TempDB Table

Why we can't take a backup of a temp database?

Answer: Temp databases, as the name says, are used to do temporary operations, such as tables, Stored Procedures and cursors. Once the operation is over it will be cleared and is minimally logged. A TempDB is recreated everytime SQL is started, so it is always has a clean copy of the database hence backup and restore operations are not allowed for a TempDB.

How you will check to determine if the SQL Server is restarted?

Answer: Check the creation date of the tempdb, if it is new it means SQL Server is started.

Figure 8: Database Properties

We can execute the following query also to check the tempdb creation date:

select name, crdate from sys.sysdatabases;

Figure 9: Output

For more information on tempdb use the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017

Resource Database in SQL Server

It is a read-only database hidden from the user. It contains all the system objects that are included with the SQL Server.
The Dbid of the resource database is 32767.
The Resource database helps when we do a SQL Server upgrade.
We can't see the resource database in SQL Server Management Studio but we can see its database file at the OS level by the name mssqlsystemresource.mdf and mssqlsystemresource.ldf in the Binn folder of Program Files.