SQL Server Database: Create, Alter, & Drop Database in SQL

What is Database?

A Database is a collection of objects such as tables, views, stored procedures, triggers, functions, etc.

Consider a with a few examples in real life:

  • We have Bookcase where Books resides,
  • We have homes where we live,
  • We have parking lots where vehicles are parked & examples are countless.

Similarly, we have DATABASE which is a kind of home for all our tables, views, stored procedures, etc which are interlinked.

Technically, Database store the data in a well-organized manner for easy access and retrieval. In SQL Server, there are two types of databases:

  1. System Databases: The system databases are created automatically for you when you install the SQL Server. They play a crucial role in the server, especially in ensuring that database objects run correctly. Examples of SQL Server system databases include: Master, MSDB, Model, Tempdb, Resource
  2. User Databases: The user databases are created by the database users like you who have been granted access to create databases

In this tutorial, you will learn:

Rules to Create a Database

First, we need to know the basic rules for creating new DB:

  • Database names must be unique within an instance of SQL Server.
  • Database names can be a maximum of 128 characters.
  • The CREATE DATABASE statement must run in an auto-commit mode.

Rules to Create a Database

Rules to Create a Database

There are 2 ways to create Database in SQL server.

  1. SQL Server Management Studio
  2. Transact-SQL

How To Create Database in SQL Server Management Studio

Here is a step by step process to create a database in SQL server management studio:

Step 1) Right Click on ‘Database’ from ‘Object Explorer’ window and then select ‘New Database.’

How To Create Database in SQL Server Management Studio

Step 2) Below ‘New Database’ screen will appear. Enter ‘Database name’.Note that: ‘Logical name’ column will be auto-populated with:

  1. Edu | Filetypes: Rows and Filegroups: PRIMARY
  2. Edu_log | Filetypes: LOG and Filegroups: ‘Not Applicable’

How To Create Database in SQL Server Management Studio

Here:

A) Edu | Filetypes: Rows and Filegroups: PRIMARY is .mdf file

B) Edu_log | Filetypes: LOG and Filegroups: ‘Not Applicable’ is .ldf file

Step 3) (Optional) For more Complex settings, we can navigate to ‘Options’ and ‘Filegroups’.At Beginner level, creating Database from General Tab will suffice.

Steps To Create Database in SQL Server Management Studio

Step 4) Click on ‘Add’.

Steps To Create Database in SQL Server Management Studio

Result: ‘Edu’ Database Created.

Process To Create Database in SQL Server Management Studio

We can expand Database – ‘Edu’ which will contain Tables, View, etc. These are initially blank until the user creates new Table, views, etc.

View the Source Query:

You can view the source query of the newly created ‘Edu’ Database as:Navigation: Right Click on Database name>Script Database as> CREATE To> New Query Editor Window.

Process To Create Database in SQL Server Management Studio

Query Window:

Process To Create Database in SQL Server Management Studio

Create Script:

USE [master]
GO

CREATE DATABASE [Edu]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Edu', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Edu_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

Create Database with T-SQL

Another method is to write the T-SQL query to Create a Database and execute it.

Let’s have a look at most Simple Database Creation T-SQL query.

Syntax:

CREATE DATABASE <Database_name>

Query:

CREATE DATABASE [Edu_TSQL_file]

Click on ‘Execute’

How to Create Database with T-SQL

Result: We can see Edu_TSQL created in SQL Object Explorer.

How to Create Database with T-SQL

Let’s have a look when we want to Create Database with .mdf and .ldf file. Here, we can give the location as an implicit part of our query.

Syntax:

CREATE DATABASE database_name   
[ CONTAINMENT = { NONE | PARTIAL } ]  
[ ON   
      [ PRIMARY ] <filespec> [ ,...n ]   
      [ , <filegroup> [ ,...n ] ]   
      [ LOG ON <filespec> [ ,...n ] ]   
];

Query:

CREATE DATABASE [Edu_TSQL_file]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Edu_TSQL_file', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_TSQL_file.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Edu_TSQL_file_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\Edu_TSQL_file_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )

How to Create Database with T-SQL

How to Alter Database

Like Create Database Query we can also Alter Database. We can rename database name, change file location and setting, etc.

Basic rules for Altering new DB:

  • The ALTER DATABASE statement must run in an auto-commit mode.
  • ALTER DATABASE is not allowed in an explicit or implicit transaction.

There are 2 ways to Alter Database in SQL server.

  1. SQL Server Management Studio
  2. Transact-SQL.

How To Alter Database in SQL Server Management Studio

Below are the steps to alter database in SQL server management studio:

Let’s try to Alter Name of our Pre-Created Database ‘Edu’.

How To Alter Database in SQL Server Management Studio

Step 1) Rename the Database

Right click on Database name. Click on ‘Rename’.

How To Alter Database in SQL Server Management Studio

Step 2) Enter the New Database Name

Database name will be editable. Enter the new Name and Press Enter.

How To Alter Database in SQL Server Management Studio

Result: The Database is now renamed as “Edu_Alter” from ‘Edu.’

Steps To Alter Database in SQL Server Management Studio

Alter Database with Transact-SQL

Now Let’s Alter Database using T-SQL

Syntax:

ALTER DATABASE <Databse_name>              
MODIFY NAME = <New Name>

Query:

ALTER DATABASE Edu_TSQL
MODIFY NAME = Edu_TSQL_Alter;

Run the above query by clicking on ‘Execute’.

Result: The Database is now renamed as “Edu_TSQL_Alter” from ‘Edu_TSQL’.

Steps to Alter Database with Transact-SQL

General Syntax:

ALTER DATABASE { database_name  | CURRENT }  
{   MODIFY NAME = new_database_name   
  | COLLATE collation_name  
  | <file_and_filegroup_options>  
  | SET <option_spec> [ ,...n ] [ WITH <termination> ] 
  | SET COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 | 90 }   
} ;

Changing .mdf/.ldf file name

Query:

Alter DATABASE Edu_TSQL_Alter;
MODIFY FILE ( NAME = Edu_TSQL, NEWNAME = Edu_TSQL_newName );

Steps to Alter Database with Transact-SQL

Changing .mdf/.ldf file location

Query:

Alter DATABASE Edu_TSQL_Alter;
MODIFY FILE ( NAME = Edu_TSQL_NewName, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_MS\MSSQL\DATA\New_File\Edu_TSQL_log.ldf' );

Steps to Alter Database with Transact-SQL

Delete Database

There are 2 ways to Delete Database in SQL server.

  1. SQL Server Management Studio
  2. Transact-SQL.

How To Drop Database in SQL Server Management Studio

Following is the process to drop a database in SQL server management studio:

Let’s try to Delete our Pre-Created Database ‘Edu_Alter.’

Step 1) Right click Database. Click on ‘Delete’ and then ‘OK.’

How To Drop Database in SQL Server Management Studio

Result: ‘Edu_Alter’ is deleted from ‘Object Explorer’ Database list.

How To Drop Database in SQL Server Management Studio

Delete Database using Transact-SQL

Let’s try to Delete our Pre-Created Database ‘Edu_TSQL_Alter.’

Syntax:

DROP DATABASE <Databse_name>

Query:

USE master;  
GO  
DROP DATABASE Edu_TSQL_Alter;  
GO

Run the above query by clicking on ‘Execute.’

Result: ‘Edu_TSQL_Alter’ is deleted from ‘Object Explorer’ Database list.

How to Delete Database using Transact-SQL

Restore Database in SQL Server

You can create a database by restoring a database you had backed up earlier. It can be done by running the restore database command which takes the following syntax:

restore Database <database name> from disk = '<Backup file location + filename>

The query should be executed within the query window just like out previous command. For example:

restore database Edu from disk = 'C:\Backup\Edu_full_backup.bak'

You can also use the GUI Navigation: Right Click Database>Restore Databases>Device> Import file>Click Ok.

Summary:

  • We can use both SQL Management GUI and T-SQL to perform all the three operations; Create, Alter and Delete Database.
  • A maximum of 32,767 databases can be specified on an instance of SQL Server.
  • System Databases cannot be deleted.
  • Create, Alter & Drop: All operations are case insensitive. We can use both upper and lower case as a syntax.