Create Database in SQL Server

How to Create Database in SQL Server– Learn to create MS SQL Server Database using SQL Server Management Studio. You can also write a script to create a new database. To create a database you should have the privilege. So make sure that you have the privilege. In MS SQL Server the default use is “sa” which is a system admin user and the system admin user has all privileges. Therefore if you go with “sa” user then you got the all privileges.

Table of Contents

1. What is Database?
2. Create a Database in SQL Server Management Studio.
3. Write a script to create a database in SQL Server.

What is Database?

In the case, of information technology data is to store digitally. Therefore data is to store in the electronic storage device. Database is the application software which manages the digital data. Database system helps to store digital data with indexing and organised way also easily read data when need it.

In this article, we discuss the process of creating a database in MS SQL Server. MS SQL Server is the famous Database Management System by Microsoft.

Create a Database in SQL Server Management Studio.

SQL Server Management Studio is a tool to manage MS SQL Server. Therefore you can create Databases with this tool easily. To create a database follow the following steps.

Step 1: Connect SQL Server SQL Server Management Studio.

Open SQL Server Management Studio then click on the “Connect” button then find the database under Database Engine. After selecting the database click on the “OK”. Also you can type the Database Instance name in the “Server Name” field.

SQL Server Authentication system

In the case of MS SQL Server two types of Authentication mode available one is “Windows Authentication” and other is “SQL Server Authentication“. So if the “Windows Authentication” enabled in the SQL Server then you can log in the Database Server otherwise you need to log in with “SQL Server Authentication Mode“. Windows authentication can access only from the local computer. So to access the Database from the network you need “SQL Server Authentication Mode”

In the case of “SQL Server Authentication” you need a user name and password. “sa” is the default system admin user of SQL Server. To enable the authentication system you should enable the “Mixed Mode Authentication” at the time of installation or you can enable it from “Security” tab under “Server Properties” window.

After connecting SQL Server with Management Studio Right Click on Database under tree view and click on “New Database“. Then you will get a New Database window where you have to enter the name of the database. If you want to change the directory of database’s files then you can change it from the “New Database” window. See the screenshot below. After enter and the name of the database and all configuration just click on OK.

This is a very simple process to create a database with a graphical user interface. But there is also a process to create a database.

Write a script to Create Database in SQL Server.

Another popular way to create a new database is executing the database creation script. To open new query window just click on “New Query” option from SQL Server Management Studio or press “Ctrl+N” then write the script for creating a database. After write just click on “Execute” option or press “F5” to execute the script. See the basic syntax to create a database.

USE master ;  
GO  
CREATE DATABASE MyDemoDB
ON   
( NAME = MyDemoDB,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyDemoDB.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = MyDemoDB_log,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyDemoDB_log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH = 5MB ) ;  
GO  

But you can create a database with the following simple script. The simple script will create a database with the default file name in the default data directory of SQL Server

Simple script to Create Database in SQL Server.

CREATE DATABASE MyDemoDB

But if you want to specify the file path then you need to mention the name of the files and the directory path.

The script to create a database with the custom file name and directory location.

USE [master]
GO

CREATE DATABASE [MyDemoDB] ON  PRIMARY 
( NAME = N'AdventureWorks_Data', 
	FILENAME = N'D:\SQL_Data\MyDemoDB_Data.mdf' , 
	SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON 
( NAME = N'AdventureWorks_Log', 
	FILENAME = N'D:\SQL_Data\MyDemoDB_Log.ldf' , 
	SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO

After creating the database you need to execute the following script to use the database.

USE DATABASE MyDemoDB