Create and Delete Database in SQL Server 2012

In this article, we will see how to create and remove a database in SQL Server 2012. SQL Server supports both system and user databases. An authorized user can create a user database, while the system database is generated during the installation of SQL Server. There are two basic methods to create a a SQL Server database, the first is the SQL Server Server Management Studio (visually) and the second is the Transact-SQL statement using Create database (Programmatically). So let's take a look at a practical example of how to create and remove a database in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

Creating a Database Visually

Now Press F8 to open the Object Browser in SQL Server Management Studio and expend it.

Database -> right-click-> select New database

This would open the "New database" window:

Now enter the database name to create the database.

Now click on the OK button to create the database. The new database will be displayed in the Object Explorer:

Deleting a Database

To delete a database Press F8 to open the Object Browser in SQL Server Management Studio and expend it. Select a database and right-click on it.

Database -> right-click-> select delete

Create a Database programmatically

This statement has the following form:

CREATE DATABASE database_name 
    [ ON 
        { [ PRIMARY ] [ <filespec> [ ,...n ] 
        [ , <filegroup> [ ,...n ] ] 
    [ LOG ON { <filespec> [ ,...n ] } ] }
          [FOR RESTORE]

Arguments

Database name

Database is the name of the new database. Database names must be unique within an instance of SQL Server and comply with the rules for identifiers. A database_name can be a maximum of 128 characters.

ON

Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined. ON is required when followed by a comma-separated list of <filespec> items that define the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items that define user file groups and their files.

PRIMARY

Specifies that the associated <filespec> list defines the primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file. 

LOG ON

Specifies the disk files used to store the database log, Log files, are explicitly defined. LOG ON is followed by a comma-separated list of <filespec> items that define the log files. If LOG ON is not specified then one log file is automatically created 
that has a size that is 25 percent of the sum of the sizes of all the data files for the database.

Example showing creation of a database

The following code is used to create a database:

CREATE DATABASE Test

ON

( NAME = Test_dat,

    FILENAME = 'C:\Temp\test.mdf',

    SIZE = 10,

    MAXSIZE = 50,

    FILEGROWTH = 5 )

LOG ON

( NAME = Test_log,

    FILENAME = 'C:\Temp\testlog.ldf',

    SIZE = 5MB,

    MAXSIZE = 25MB,

    FILEGROWTH = 5MB ) ;

Output

 

The database has been created successfully.



In the preceding example we create a database which has the name test. To see the created database refresh the Object Browser.

 

Dropping a database

Removes one or more databases or database snapshots from an instance of SQL Server. 

Syntax

DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] [;] 

database_name: specifies the name of the database to be removed. To display a list of databases, use the Sysdatabases catalog view.

 

database_snapshot_name: specifies the name of a database snapshot to be removed.

Dropping a single database

The following example removes the test database.

Drop database Test

Dropping multiple Databases

The following example removes the Test, Employee and Registration databases:

DROP DATABASE Test, Employe, Registration;

Dropping a database snapshot

The following example drops a database snapshot, named Test_snapshot0600, without affecting the source database:

DROP DATABASE Test_snapshot0600;