Basics of Database Administration in SQL Server : Part 1

Databases in SQL Server

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

1. System Databases.
2. User Databases.

System Databases
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 Database2

Master Database

• 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 very important database and we must have the backup.
• Without the master database the server can't be started.
• Interview Question: 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 the 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;

Figure 3: Master Database

For more information on the master database use the following link:
https://msdn.microsoft.com/en-us/ms187837

Model Database

• 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:

1. SELECT name, physical_name FROM sys.database_files;  

Figure 5: Data Table

Interview Question: 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://msdn.microsoft.com/en-us/ms186388

MSDB Database

• 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; 

Figure 6: MDB database Table

For more information on the msdb database use the following link:
https://msdn.microsoft.com/en-us/ms187112

TempDB

• 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;

Figure 7: TempDB Table

Interview Question: 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.

Interview Question: 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:

1. select name, crdate from sys.sysdatabases;

Figure 9: Output

For more information on tempdb use the following link:
https://msdn.microsoft.com/en-us/ms190768

Resource Database

• 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.

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:
http://blogs.msdn.com/b/vsanil/archive/2012/11/02/resource-database-common-questions.aspx

What User Databases are: User databases are databases created by the user themself.


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 :

• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

Have a great day. Keep sharing your knowledge !!