How to Create a Database in SQL Server Using Command Line?

In this SQL Server tutorial, you will understand how to create a database in SQL Server using command line.

I will show you a step-by-step process from connecting to the SQL Server instance using the SQLCMD utility to creating a new database. Also, you will understand the error that appears when you create a database that already exists.

Table of Contents

Create Database in SQL Server Using Command Line

To create a database in SQL Server using the command line, follow the below steps:

Open the command line or command prompt on your computer by pressing CMD + R and type the cmd in the box, then hit enter.

Create Database in SQL Server Using Command Line Opening Command Prompt

Next, connect to the SQL Server instance using the below command in your command line.

sqlcmd -S hostname\server_instance_name

Where hostname\server_instance_name is the name of the SQL Server instance, replace this name with your SQL Server name. If you have installed SQL Server Management Studio, then you can find the name of the SQL Server instance.

In the above picture, connecting to the SQL Server instance ‘MSI\SQLEXPRESS’ but this SQL Server instance name can be different from your computer, it depends upon how you have configured the SQL Server.

The SQLCMD is a type of utility in SQL Server that allows you to write Transact-SQL statements, scripts, and system procedures with the help of files using different modes.
The different modes are Windows script file, Command prompt, Query editor, etc.

After entering the command to connect to the SQL Server instance, press enter from your keyboard and now you are connected to the SQL Server as shown in the below picture.

Create Database in SQL Server Using Command Line Connected to SQL Server Instance

If you see something like 1> in your command line, it means you are connected to the SQL Server instance.

Now, you can execute the SQL query to create a database. To create a database in SQL Server using the command line, follow the below syntax.

CREATE DATABASE new_database_name;
GO

Where,

CREATE DATABASE: It is the command to create a database.
new_database_name: Name of the new database that you want to create in SQL Server.
GO: It is a command that is executed after each SQL query to run the query. If you write a query and hit enter, your query will not work. To run that query, use GO in the SQL server command line.

Now type the below query in your command line to create a database named ‘Orders’.

CREATE DATABASE Orders;
GO

Create Database in SQL Server Using Command Line

In the above picture, a database named ‘Orders’ is created. To check the whether database is created or not, use the below command to view all the databases.

One thing you need to remember while specifying a new database name, always follow the best practices

SELECT name FROM sys.databases;
GO

how to create a database in sql command line

After running the above query, you can see the newly created database ‘Orders’ in the output. This is how you can create a database in SQL Server using the command line.

The sys.databases contain all the systems databases in SQL Server.

Before creating a new database, always ensure that the database of the same name doesn’t already exist in your SQL Server.

Let’s again create the same database with the name ‘Orders’ and see what kind of error the SQL server generates for already existing databases.

Create Database in SQL Server Using Command Line Already Existing Database

You can see that it shows the error ‘Database Orders already exists’, which means the database that you are trying to create already exists in the SQL Server.

The solution to this kind of error is to create a new database with a different name or you can connect to an existing database.

Conclusion

In this SQL Server tutorial, you learned how to create a database using the command line in SQL server. After that, you learn how to solve the error that occurred while creating a database.