Trending September 2023 # Sql Server Database: Create, Alter, &Amp; Drop Database In Sql # Suggested October 2023 # Top 18 Popular

You are reading the article Sql Server Database: Create, Alter, &Amp; Drop Database In Sql updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Sql Server Database: Create, Alter, &Amp; 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:

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

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

There are 2 ways to create Database in SQL server.

SQL Server Management Studio

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 2) Below ‘New Database’ screen will appear. Enter ‘Database name’.Note that: ‘Logical name’ column will be auto-populated with:

Here:

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

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:

Query Window:

Create Script:

USE [master] GO CREATE DATABASE [Edu] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Edu', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQL_MSMSSQLDATAEdu.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'Edu_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQL_MSMSSQLDATAEdu_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:

Query:

CREATE DATABASE [Edu_TSQL_file]

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 [ ON ];

Query:

CREATE DATABASE [Edu_TSQL_file] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Edu_TSQL_file', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQL_MSMSSQLDATAEdu_TSQL_file.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'Edu_TSQL_file_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL14.SQL_MSMSSQLDATAEdu_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:

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.

SQL Server Management Studio

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

Step 1) Rename the Database

Step 2) Enter the New Database Name

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:

Query:

ALTER DATABASE Edu_TSQL MODIFY NAME = Edu_TSQL_Alter;

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

General Syntax:

{ MODIFY NAME = new_database_name } ;

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 FilesMicrosoft SQL ServerMSSQL14.SQL_MSMSSQLDATANew_FileEdu_TSQL_log.ldf' );

Delete Database

There are 2 ways to Delete Database in SQL server.

SQL Server Management Studio

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

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:

Query:

USE master; GO DROP DATABASE Edu_TSQL_Alter; GO

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

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:

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

restore database Edu from disk = 'C:BackupEdu_full_backup.bak' 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.

You're reading Sql Server Database: Create, Alter, &Amp; Drop Database In Sql

Update the detailed information about Sql Server Database: Create, Alter, &Amp; Drop Database In Sql on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!