SQL Server Database: Create, Alter, Drop, Restore

Details

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:

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:

There are 2 ways to create Database in SQL server.

  1. SQL Server Management Studio
  2. Transact-SQL

Create Database using SQL Server Management Studio

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

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'

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.

Step 4) Click on 'Add'.

Result: 'Edu' Database Created.

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.

Query Window:

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'

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

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 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:

There are 2 ways to Alter Database in SQL server.

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

Alter Database with SQL Server Management Studio

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

Step 1) Right click on Database name. Click on 'Rename'.

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

Result: The Database is now renamed as "Edu_Alter" from 'Edu.'

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

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

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' );

Delete Database

There are 2 ways to Delete Database in SQL server.

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

Delete Database 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.'

Result: 'Edu_Alter' is deleted from 'Object Explorer' Database list.

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.

Restore Database

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:

 

YOU MIGHT LIKE: