SQL Server Create\Drop Database

SQL Server Create\Drop Database

Introduction

The goal of this article is to create\drop an Employees database in SQL Server and populate it with test data in three quick steps. No theory or lengthy technical explanations are given. Just concise instruction for those that want to quickly see what SQL Server is. If you are familiar with Access, MySQL, Oracle, or some other data store application, then this article can serve as the “Hello World” sample application for you. Two different methods can be used to manage SQL Server, the GUI (Graphical User Interface), and the command line. Both will be covered starting with the GUI version. This article assumes SQL is already installed.

A maximum of 32,767 databases can be specified on an instance of SQL Server.

Create a Database

Using GUI 
A “Database” in SQL Server is a container that holds a group of related objects. In addition to storing the actual data, a Database also stores the structure of how that data is saved (called a Table), and any related helper objects such a Queries (saved ways to look data up). To begin, we’ll create a Database using the graphical interface called the “SQL Server Management Studio”.
From the Windows Start Menu, select “Microsoft SQL Server”, and then “SQL Server Management Studio”. Once the Management Studio starts, right click the Databases folder and select new Database. Enter a name in the “Database name” text box. For this example, well use the name “Test”.

As you the type the database name in, the Logical Name for the file types Data and Log will automatically fill in as well. The “Data” file is where the actual data is saved on the hard drive. The “Log” file keeps track of any changes to that data. Click OK.
You have created empty database.
Working with the Command Line

1. Connect to the Database Engine.
   
2. From the Standard bar, click New Query.
   
3. Copy and paste the following example into the query window and click Execute. This example creates the database Sales. Because the keyword PRIMARY is not used, the first file (Test) becomes the primary file. Because neither MB nor KB is specified in the SIZE parameter for the Test file, it uses MB and is allocated in megabytes. The Test_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.
   
   

   
   USE master ;
   GO
   CREATE DATABASE Test
   ON 
   ( NAME = Sales_dat,
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\Test.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
   LOG ON
   ( NAME = Sales_log,
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\Test_log.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB ) ;
   GO
   

Drop a Database

Follow below steps to drop or delete the database:

Using GUI
1.Connect to the Database Engine.
2. Right click on the database
 
Working with the Command Line

1. Connect to the Database Engine.
   
2. From the Standard bar, click New Query.
   
3. Copy and paste the following example into the query window and click Execute. The example removes the Sales databases.

USE master ;
GO
DROP DATABASE Sales;
GO