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:
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:
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:
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:
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.
Figure 10: Microsoft SQL System
We can see the location of the resource database file using the following 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 the resource database use the following link:
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 !!
Hari LakkakulaPosted Jul 11, 2018, 12:40 PM
We can take master db backup?
Debaditya MondalPosted Nov 8, 2016, 1:17 AM
Nice and helpful article ..but i have a question..why msdb creation time differ than master?
Ankur MistryPosted Dec 22, 2015, 1:12 PM
Thanks for sharing nice article
Dominique CejaPosted Jun 29, 2015, 1:25 PM
Great information. Simple and clear!
Yashwant VishwakarmaPosted Jun 14, 2015, 12:51 AM
Thanks Santhakumar
Santhakumar MunuswamyPosted Jun 12, 2015, 2:30 PM
Thanks for nice article :)
Moise SAWADOGOPosted Jun 12, 2015, 11:05 AM
Simple & Clear ! thanks.
Yashwant VishwakarmaPosted Jun 12, 2015, 7:46 AM
Thank You Pankaj :-)
Jaipal ReddyPosted Jun 12, 2015, 7:43 AM
Nice one. ! keep explore the treasure of knowledge.
Pankaj Kumar ChoudharyPosted Jun 12, 2015, 6:08 AM
Really This article develop new concepts about system database ... Thanks For sharing this Article @Yashwant Sir.........
RakeshPosted Jun 12, 2015, 5:54 AM
Good information
Sibeesh VenuPosted Jun 12, 2015, 1:44 AM
Good One.
NitinPosted Jun 12, 2015, 1:35 AM
good one