Basics of Database Administration in SQL Server: Part 1

In this article, we will learn the basics of SQL Server Database Administration.Learn four system databases in SQL Server, master, msdb, tempdb and model.

Databases in SQL Server

 
There are basically the following two types of databases in SQL Server:
  1. System Databases.
  2. 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.
 
System Database in SQL Server 
Figure 1: System Database
 
System Database in SQL Server 
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:
  1. SELECT name, physical_name FROM sys.database_files;
Master Database in SQL Server 
Figure 3: Master Database
 
For more information on the master database use the following link:
 
 

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.

    Model Database in SQL Server
    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:
  1. SELECT name, physical_name FROM sys.database_files;  
Model Database in SQL Server 
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:
 
 

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:
  1. SELECT name, physical_name FROM sys.database_files; 
MSDB database Table 
Figure 6: MSDB database Table
 
For more information on the msdb database use the following link:
 
 

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:
  1. SELECT name, physical_name FROM sys.database_files;
TempDB Table in SQL Server 
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.
 
SQL Server Database Properties 
Figure 8: Database Properties
 
We can execute the following query also to check the tempdb creation date:
  1. select name, crdate from sys.sysdatabases;
tempdb creation date 
Figure 9: Output
 
For more information on tempdb use the following link:
 
 

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.
Microsoft SQL System 
Figure 10: Microsoft SQL System
 
We can see the location of the resource database file using the following query also:
  1. Use master GO  
  2. SELECT  
  3. 'ResourceDB' AS 'Database Name',  
  4. NAME AS [Database File],  
  5. FILENAME AS [Database File Location]  
  6. FROM  
  7. sys.sysaltfiles  
  8. WHERE  
  9. DBID = 32767 GO
For more information on the resource database use the following link:
 
 
What User Databases are: User databases are databases created by the user themself.
 
User Database 
Figure 11: User Database
 
This is all about the SQL Server databases. I hope you liked it. To know more about SQL Server Database Administration click on below links :
Have a great day. Keep sharing your knowledge !!