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.
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.
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.
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
- 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
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
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.
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.
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.
You may also like:
- Create a Database in SQL Server Using Query
- Create a Database in SQL Server Management Studio
- Change Database Name in SQL Server using Query
- How to Get Connection String in SQL Server Management Studio
- How to Change Database Owner in SQL Server?
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.