SQL Server Interview Questions and Answers for DBA Series 1

What is Database?

Database is collection of tables which used to store a specific structured data which can be used by the application or user.

What are the system databases and what are their functions?

System database are used to store system information. There are five system databases each one having its own functionality.

1.      Master DB

2.      MSDB

3.      Model

4.      Resource

5.      Temp

Master Database: it stores all the system related information for an instance of SQL Server. It stores the metadata for the database which created in SQL Server Instances.

MSDB Database: it informs the information and activities related to SQL server agent.

Model Database: It is the template to create a new database in SQL server instance .if you have created some object in it will reflect in all database which were created after this until you won’t remove these objects from model database.

Resource Database: Resource database all the system objects views and procedures.

Temp Database: It is used to store temporary objects which create during the execution of query. SQL Server creates a free copy of temp dB whenever server starts. Backup operation is not allowed for the temp DB.

What are the files in SQL Server?

SQL Server stores the data in data files on hard disk.

What are different types of database files in SQL Server?

SQL has three types of database files:

Primary files: Stores the information of the database related to start-up, data and other files details. A database can have one primarily data base file and the file extension for this is .mdf.

Secondary files: Stores the user data. A database can have multiple secondary files which can help user data to spread across the different hard disk.

Transaction logs files: Transaction log files used to store the transactional log.

What are the different states of the database?

Different database states are as below mentioned.