SQL Server Databases Overview

Databases in SQL Server
There are basically two types of databases in SQL Server

1. System Databases
2. User Databases

1. System Databases:
1.1. What are System Databases:
System databases are those databases which gets created when SQL Server install. These databases are used for different operational & management activity for SQL Server.
 
1.2. Types of System Databases:
There are basically four system databases in SQL Server namely master, msdb, tempdb, model  which we can see. Apart from that there is one more system database resource database which is hidden and read-only. Let’s move ahead with each system database.       1.2.1. Master Database:

• All the system level information for a SQL Server record by master database.

 

• The dbid (database id ) of master is 1.

 

• Master  database have SIMPLE RECOVERY MODEL.

 

• It is very important database and we must have the backup.

 

• Without master database server can’t be started.

 

• Interview Question: Suppose if master database files missing or inaccessible, will SQL Server start or up?

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

• Master database contains information about server configuration. We can see the server configuration with below query:

select * from sys.sysconfigures;
 

• Master database contains the information about all other databases & their location on SQL Server. We can see these information with executing below query:

              select * from sys.sysdatabases;   or   sp_helpdb
 

• master database contains information about logins in SQL Server. Below is the query by which we can see it:

select * from sys.syslogins;
 

• master database also contains information about users on SQL Server. Below is the query to see user details:

select * from sys.sysusers;
 

• master & mastlog are the logical file names of master database.

master.mdf ( data file ) & mastlog.ldf are the physical files of master database.
 
Query to see the physical file location of master database:
select name, physical_name FROM sys.database_files; 
 
For more information on master database follow below link:
https://msdn.microsoft.com/en-us/ms187837
 
 
1.2.2. Model Database:

• Model database act as a template database used in creation of new databases.

 

• dbid of model database is 3.

 

• By default model database has FULL RECOVERY MODEL.

 

• We can take the backup of model database.

 

• modeldev & modellog are the logical file names of model database.
• model.mdf ( data file ) & modellog.ldf are the physical files of model database.

 

• Same query  can be use to see the physical file location of model database
• select name, physical_name FROM sys.database_files;

 
 
Interview Question: A user has created a new database what will be the recovery model of that database?
 
Answer: Because model database is act as a template database , so when a user will create new database it will inherit the property of model database and as we know by default recovery model of model database is FULL ( until or unless user change it ), hence new database created by user has also FULL RECOVERY MODEL.
 
For more information on model database follow below link:
https://msdn.microsoft.com/en-us/ms186388          
 
1.2.3. MSDB Database:

• MSDB database stores information related to backups, SQL Server Agent information, SQL Server Jobs, alerts etc.

 

• dbid of msdb database is 4.

 

• Recovery model of msdb database is SIMPLE.

 

• We can take backup of msdb database.

 

• MSDBData & MSDBLog are the logical file names of msdb database.
• MSDBData.mdf ( data file ) & MSDBLog.ldf are the physical files of msdb database.

 

• Same query  can be use to see the physical file location of msdb database:
• select name, physical_name FROM sys.database_files;  

 
                                                                                                                                    
For more information on msdb database follow below link:
https://msdn.microsoft.com/en-us/ms187112
 
1.2.4. TempDB:

• It stores temporary objects like temporary tables, temporary stored procedures, temporary tables to store sorting etc.
• dbid of temp database is 2.

 

• Recovery model of temp database is SIMPLE.

 

• We can’t take backup of tempdb.
• It is created everytime when SQL Server restarted.

 

• tempdev & templog are the logical file names of tempdb.
• tempdb.mdf ( data file ) & templog.ldf are the physical files of tempdb.

 

• Same query  can be use to see the physical file location of tempdb
• select name, physical_name FROM sys.database_files;  

 
             
 

• Interview Question: Why we can’t take backup of temp database?

 
Answer: Temp database as the name says it is used to do the temporary operations such as tables, stored procedures, cursors. Once the operation is over it will be cleaned & is minimally logged. TempDB is recreated everytime when SQL is started, so it is always have a clean copy of database hence backup & restore operations are not allowed in TempDB.
 

• Interview Question: How you will check SQL Server is restarted or not?

 
           Answer: As we know tempdb created everytime when SQL Server restarts hence check the creation date of tempdb, if it is new it means SQL Server is started again.
 
we can execute below query also to check tempdb creation date:
select name, crdate from sys.sysdatabases;
 
 
For more information on tempdb follow below link:
https://msdn.microsoft.com/en-us/ms190768
                                                        
 
1.2.5. Resource Database:

• It is a read-only database which is hidden from user. It contains all the system objects that are included with the SQL Server.

 

• dbid of resource database is 32767.

 

• Resource database help when we do SQL Server upgrade.

 

• We can’t see resource database in SQL Server Management Studio but we can see its database file at OS level by the name of mssqlsystemresource.mdf & mssqlsystemresource.ldf in Binn folder of Program Files.

 
 

• We can see location of resource database file using below query also:

Use master
GO
 
     SELECT 'ResourceDB' AS 'Database Name' , NAME AS [Database File]
     , FILENAME AS [Database File Location]
     FROM sys.sysaltfiles
     WHERE DBID = 32767
     GO
 
 
 
For more information on resource database follow below link:
http://blogs.msdn.com/b/vsanil/archive/2012/11/02/resource-database-common-questions.aspx
 
1.2. What are User Databases:
User databases are those databases which are created by user itself.
 
 
This is all about the SQL Server databases hope you will like it. I will come with more in future…
 
Have a great day :-)

Regards,
Yashwant Vishwakarma | http://www.sqlocean.com