Hello Friends, in this tutorial I am going to tell you about System Databases in SQL Server. Also, we are going to learn how to create a Database in SQL Server using two different approaches.

After completing this tutorial you will be able to understand:

  • System Databases in SQL Server.
  • How to Create a Database in SQL Server using GUI.
  • How to create a Database in SQL Server using TSQL statements.

Introduction:

When you install SQL Server, some databases are automatically installed on your with SQL Server is called System Databases. These Databases are responsible for storing some important kind of information such as configuration setting, information about all databases. These databases also used to track activities.

There are five system Databases available:

  • Master Database.
  • Model Database.
  • Msdb Database.
  • Tempdb Database.
  • Resource Database.

System Databases

1- Master Database: Master Database is a most important database of SQL Server. Master Database contains the configuration setting about server and important information. This information can be about logins, linked server, end points and additional system-related configuration. Master Database also contains information about all databases installed on your current instance of the SQL Server. When a new database is created by a user the default entries corresponding to that database also created in a master database. It is recommended to take backup of your master database. Also, avoid changing your master database because of changes in your master database corrupt your entire server.

2- Modal Database:  Modal Database acts as a template while creating new Database in SQL Server. When you create a new Database in your SQL Server all the objects of Modal Database are automatically copied to that Database. Any modification made to the Modal Database is automatically reflected all Database exists on the server.

3- Msdb Database:  The msdb Database contains configuration information about various services like Database Mail, SQL Server Agent, Service Broker, Database backups, Replication. The most important thing about msdb Database is that it keeps backup history. You can easily get information when each database and filegroup was last backed up.

4- Resource Database:  Resource Database is a hidden Database that contains all system objects. Since we cannot make changes to Resource so it is a read-only Database. We cannot see Resource Database however we can find data files associated with Resource Database to location:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

The Resource Database was introduced with SQL Server 2005 to manage upgrade and rollback of System objects.

5- Temdb Database: Temdb is used to store temporary objects like temporary tables (prefixed with # such as #tablename or ##tablename), table variables, cursor etc. The temdb Database is re-created every time it is restarted.

So, we have made a discussion on System Database. Let’s create a user Database in SQL Server.

How to create a Database in SQL Server?

We can create a Database in SQL Server using one of two approaches:

Using GUI:

You can create a database in SQL Server using GUI. Follow the steps:

Step 1-  Open and connect to your server the first screen you will get as in below snapshot:

System Databases

Step 2- Right Click on Database and chose “New Database”.

System Databases

Step 3- Enter Database Name and click OK.

System Databases

That’s all your Database has been created successfully. You can find your Database by expanding Database Node on Left-hand side.

Using TSQL statement:

The “CREATE DATABSE” statement is used to create a Database in SQL Server. Here is the syntax for creating a Database:

CREATE DATABASE <databsename>

Example:

CREATE DATABASE MyDatabseUsingTSQL

Conclusion:

I hope you liked this post about System Databases in SQL Server. I will continue writing on useful topics.

Thank You

Leave a Comment