SQL CREATE DATABASE Statement

Administrators can establish a new database with the restore task option using a SQL Server database backup file. I'd like to demonstrate how SQL database administrators and SQL developers can use SQL Server Management Studio's Restore task to create a new database from a backup file.

On my development machine, I have a SQL Server 2014 instance running. I also downloaded the AdventureWorks example database backup file, which I intend to use to create a new database on my SQL Server installation using the AdventureWords database backup file.

• First, launch SQL Server Management Studio (SSMS).
• Connect to SQL Server instance which you want to create AdventureWorks database.
• Right click on Databases node.
• Select Restore Database... context menu option.
• restore database backup on SQL Server Management Studio.

Switch to the Device on Source section of the General tab of the Restore Database wizard to point to the database backup file of the AdventureWorks example database.

Click on "..." button.

select SQL database backup source:

• Select backup devices dialog screen will be displayed.
• Keep default option File for Backup Media Type unchanged.
• add backup media for SQL backup files.

Press Add button

On the File Explorer screen, navigate to the backup file.

For this SQL article, I'm using the AdventureWorks backup file from SQL Server 2014 to demonstrate how to create a new database with a backup file.

SQL Server database backup to restore

Click OK

In the Destination area, type the name of the SQL Server database you want to generate. If you use a database name that does not exist on the SQL Server instance, the database will be successfully created, which is what we want for this lesson.

create new database from SQL backup file

Press OK button

If no unexpected errors occur, SQL Server will successfully construct a new database from the backup file.

1. Create a database if not exists in PostgreSQL

Unlike MySQL and other databases, PostgreSQL does not support the CREATE... IF NOT EXISTS syntax. However, the \gexec argument in psql can be used to imitate this.

SELECT 'CREATE DATABASE < your db name>'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '< your db name>')\gexec

\gexec transmits the current query buffer to the server, then treats each column of each row of the query's response (if any) as a SQL statement that must be evaluated. In psql, \gexec cannot be used with the -c option.

2. Another alternative that is convenient for shell scripts:

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = '< your db name>'" | grep -q 1 | psql -U postgres -c "CREATE DATABASE < your db name>"

3. MySQL CREATE DATABASE

The command line can be used to create a database in SQL. Although database management systems (DBMS) are frequently used to establish databases, knowing the command to use, which is relatively basic, is essential.

Syntax :

To create a database that will be called “my_base” just use the following query which is very simple:

CREATE DATABASE my_base

Base of the same name that already exists

If a database with this name already exists in MySQL, the query will fail. It is recommended that you use the following query for MySQL to prevent getting this error:

CREATE DATABASE IF NOT EXISTS my_base

If a database with the same name already exists, the IF NOT EXISTS option simply does not produce an error. There will be no overwriting of the database.

We use a source database that has a table with data and a duplicate of the table with data from the source database in a second database.

Creating a table in SQL Server

Now we create a table named employee using:

CREATE TABLE [dbo].[Employee]  
(  
    [EmpID] [int] NULL,  
    [EmpName] [varchar](30) NULL,  
    [EmpSalary] [int] NULL  
)  

The following is the sample data for the employee table:

Method 1: Copy Table using SELECT INTO

Only the schema and data of a table are copied with this command. The Select into command is used to copy data from a table in one database to a table in another database. Select into can also be used to make a new table in a different database. The general syntax to do that is:

Syntax:

SELECT * INTO DestinationDB.dbo.tableName FROM SourceDB.dbo.SourceTable

Example: In the master database, the employee table is established. The source database is another name for it. You now wish to replicate the data from the master database table to the model database. It is defined using the following query:

SELECT * INTO Model.dbo.[Employee] FROM Master.dbo.[Employee]

Method 2: Generating Script in SQL Server

If you want to copy all objects, indexes, triggers, constraints etc then do it using "Generate Scripts...". Suppose we have a database named Test. Now right-click on the Test database and select the "Generate Scripts..." option.

database Name -> "Tasks" -> "Generate Scripts...."

• Now click on "Generate Scripts...". The Generate Scripts wizard will be opened.
• Now click on the "Next" Button and select tables and Stored Procedures.
• Now click on the "Next" Button and provide the proper name with path of the file.
• Now click on the "Next" Button and review your source and target location.
• Generating Script in SQL Server
• Now click on the "Next" Button.
• Generating Script in SQL Server
• Now finally click on the "Finish" button.

The script file has been generated for the Test database. To see the generated script file, select the location of the file in your computer.

1. Creating a Database in SQL Server

These are the following steps to create a new database:

• 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 a database name to create a database
• Now click on the OK button to create the database. The new database will be shown in the Object Explorer
• Now the database, named DatabaseTest, has been created.

2. Copy Database Schema and Data to Other Database

Right-click the script file and open it in Notepad, then copy and paste all of the data into SQL Server's query box. It will resemble the image below:

Copy Database Schema

All you have to do now is rename the database from test to DatabaseTest.

To inspect the schema and data, hit F5 to run the script and expand databaseTest.