SQL Server Databases: Back To Basics

  • Article

Databases in SQL Server:

Although this is very common, basic topic but I tried to make it little bit different. I tried to summarise all the basic information here with code snippets, screenshots and questions-answers. I tried to give information in a way so that every newbie can grasp SQL Server databases easily. So without wasting time let's begin:

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.

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;

Note: I have taken only few rows in snapshot.

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

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.

I tried to take backup of tempdb database but SQL Server Engine returns the above message saying backup and restore operations are not allowed on database tempdb. Hence it is very clear that we can't take backup of tempdb database.

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

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.

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

Answer: Check the creation date of tempdb, if it is new it means SQL Server is started.

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

**References: **

  1. Master Database
  2. Model Database    
  3. MSDB
  4. TempDB
  5. Resource database

A word of Thanks:
*I want to say my warm thank you to all MVPs of SQL Server. I learnt a lot from their articles, blogs and post and still learning day by day.
I am like a kid in front of all MahaGuru's of SQL Server. They have very deep knowledge about SQL Server and spreading their knowledge all over the globe. I am also trying to follow the same path. 
Paul Randal , Glenn Berry, Jes Borland, Jason Strate are my favourites. Special Thanks to Shashank Singh aka Shanky, Albert Morillo, Olaf Helper and Erland Sommarskog who always helped me here in this community to know and understand the SQL Concepts.
*

Conclusion

This is all about the SQL Server databases which I know. This article is basically emphasizes on basic concepts so that any newbie can grasp it easily. Your feedback and suggestions are welcome so that I can improve more in future.