Creating Altering and Deleting database in SQL Server

Creating Altering and Deleting database in SQL Server

In our last article of SQL Server tutorials, we discussed that using SSMS (SQL Server Management Studio), we can connect to the SQL Server database. Here we will learn about creating altering and deleting database in SQL Server.

In SQL Server we are going to interact with 2 types of databases such as

  1. System databases
  2. User databases

System databases

It contains four databases, those are

Master database:

It will store all system level information like system id, culture, server id no, server version, server culture, etc

Model database:

The model database will act as a template for creating new databases under a server environment.

Msdb (Microsoft database):

Microsoft database will store jobs and alerts information i.e. backup file information.

Tempdb:

It is a temporary database location which is allocated by the server when the user connected to the SQL Server for storing temporary table information.

creating altering and deleting database in SQL Server

Note: Once we disconnected to the SQL Server, the temporary database location will be destroyed automatically.

Note: The above system database is maintained and managed by the system by default.

User databases

These databases are created by the user for storing business related information such as employee details, Customer Details, Student details, Product Details, Salary details, etc.

A SQL Server database can be created, altered and dropped in two ways

  1. Graphically using SQL Server Management Studio (SSMS) or
  2. Using a Query
To create the database graphically
  1. Right Click on Databases folder in the Object Explorer
  2. Select New Database
  3. In the New Database dialog box, enter the Database name and click OK as shown below.

creating database in SQL Server

To create the database using a query

The syntax for creating a database:

Create database <Database Name>

Example:

Create database MyDB1

Select the above query and Click on either Execute option or F5 keyboard for execute.

Whether we create a database graphically using the designer or using a query, the following 2 files get generated.

.MDF file -Master Data File (Contains actual data)

This file will store all Tables data and will be saved with an extension of .mdf (master data file)

.LDF file – Transaction Log file (Used to recover the database)

This file will store transaction Query information (insert, update, delete. Create, etc) and saved with an extension of .ldf (log data file)

NOTE: The above two files are used for transferring the required database from one system to another system or from one location to another location.

Root Location of .mdf and .ldf files:

C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

To alter a database, once it’s created 

Alter database DatabaseName Modify Name = NewDatabaseName

Alternatively, you can also use the system stored procedure

Execute sp_renameDB ‘OldDatabaseName’,’NewDatabaseName’

To Delete or Drop a database

Drop Database DatabaseThatYouWantToDrop

When we drop a database, it deletes the LDF and MDF files.

We cannot drop a database if it is currently in use. We get an error stating – Cannot drop database “NewDatabaseName” because it is currently in use. So, if other users are connected, we need to put the database in single user mode and then drop the database.

Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate

With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.

Note: System databases cannot be dropped.

In the next article, I will discuss Creating and working with database tables in SQL Server.

SUMMARY

In this article, I try to explain creating altering and deleting database in SQL Server step by step. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *