Reader Level:
ARTICLE

Create and Delete Database in SQL Server 2012

Posted by Rohatash Kumar Articles | SQL Server September 19, 2012
In this article, we will see how to create and remove a database in SQL Server 2012.
  • 0
  • 0
  • 5341

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

Create-new-database-in-sqlserver.jpg

This would open the "New database" window:

New-Database-Window-in-sqlserver.jpg

Now enter the database name to create the database.

NewDatabase-window-with-database-name-in-sqlserver.jpg

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

Object-Explorer-with-new-database-in-sqlserver.jpg

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

Delete-Database-in-sqlserver.jpg

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.

Create-new-database-programatically-in-sqlserver.jpg

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

 

Object-Explorer-with-new-database-in-sqlserver.jpg

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


Drop-command-in-sqlserver.jpg

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;

COMMENT USING

Trending up