How to create rename delete database in SQL Server 2008,2012,2014,2016 management studio

Today we will Learn about How to create,rename,delete database in SQL Server 

Here I tried to focus on the people who are new to the SQL Server database and if they want to create/rename/delete the database.

Steps to be followed for building a database from scratch using SSMS

Step 1: Right click on Database -> Select New Database

Step2: Give database name that you want to create (E.g. Test) and then click on OK

When we go and see the files in highlighted path, there will be two files (one with test.mdf and another with test_log.ldf) created as same as logical name. We can also give our own names in the file name section.

Sometimes we may see an error like

Cannot create file 'C:\Program Files\Microsoft SQL Server\..... DATA\test.mdf' because it already exists. Change the file path or the file name, and retry the operation.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170).

In order to resolve above issue either we have to change the Path for MDF and LDF or we have to give other file names which are not mapped to any database

T-SQL Scripts for creating SQL Server Database:

USE [master] GO CREATE DATABASE [test]  ON  PRIMARY ( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf' ) -–Change the path accordingly  LOG ON ( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_log.ldf') -–Change the path accordingly  LOG ON

T-SQL Scripts for Renaming SQL Server Database:

EXEC sp_renamedb [olddbname],[newdbname] e.g.EXEC sp_renamedb 'Test123','Test_Demo' OR  ALTER Database [dbname]  Modify name=New DBname e.g ALTER Database Test123  Modify name=Test_Demo

 T-SQL Script for Deleting SQL Server Database: 

DROP DATABASE [dbname]

Please watch below video for more details