Welcome readers, in this tutorial, we feature a comprehensive article in SQL clone database. Specifically we will understand how to clone an existing database and its content.
1. Introduction
In SQL, Database Cloning represents a feature of ensuring backup of all or specific databases. The feature is implemented for purposes,
- Data restore that was mistakenly modified or deleted
- For application development and testing purposes
- For data extraction and manipulation services
To start with this tutorial, we are hoping that users at present have their preferred database installed on their machines. For easy usage, I am using MySQL on a Windows operating system. If someone needs to go through the MySQL installation, please watch this video.
2. SQL Clone Database Example
Here is a systematic guide and we are using MySQL Database and Workbench. For implementation, we will clone the database1 to database2.
2.1 Creating Mock Data
The following SQL script will help readers to create a sample table with mock data in database1.
Script 1
/* SQL TO CREATE DATABASE. */
CREATE DATABASE IF NOT EXISTS database1;
/* USE THE NEWLY CREATED DATABASE. */
USE database1;
/* QUERY #1: SQL TO CREATE THE TABLE WITH PRIMARY KEY FOR ONE COLUMN. */
CREATE TABLE IF NOT EXISTS table1 (
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
email VARCHAR(50) NOT NULL,
gender VARCHAR(50),
PRIMARY KEY (id)
);
/* QUERY #2: SQL TO INSERT THE DATA INTO THE TABLE. */
INSERT INTO table1 (id, first_name, last_name, email, gender) VALUES (1, 'Walton', 'Yearnsley', 'wyearnsley0@list-manage.com', 'Male');
INSERT INTO table1 (id, first_name, last_name, email, gender) VALUES (2, 'Zenia', 'Arendt', 'zarendt1@comcast.net', 'Female');
INSERT INTO table1 (id, first_name, last_name, email, gender) VALUES (3, 'Gusti', 'Treend', 'gtreend2@nasa.gov', 'Female');
INSERT INTO table1 (id, first_name, last_name, email, gender) VALUES (4, 'Sidney', 'Hullbrook', 'shullbrook3@ycombinator.com', 'Male');
INSERT INTO table1 (id, first_name, last_name, email, gender) VALUES (5, 'Gallard', 'Golsby', 'ggolsby4@about.me', 'Male');
/* QUERY #3: SQL TO FETCH THE DATA FROM THE TABLE. */
SELECT * FROM table1;
If everything goes well, the table will be created and the data will be shown.
2.2 Creating a Clone of Existing Database
The following SQL script will create a new database named database2. This new database will then clone the database1.
Script 2
/* SQL TO CREATE DATABASE. */ CREATE DATABASE IF NOT EXISTS database2; /* USE THE NEWLY CREATED DATABASE. */ USE database2; /* QUERY #3 :: TO MAKE A CLONE OF EXISTING DB FROM THE OLD DB. */ /* This command will clone the old database tables and records as well. */ CREATE TABLE database2.table2 AS SELECT * FROM database1.table1; /* QUERY #3 :: SQL TO FETCH THE DATA FROM THE TABLE. */ SELECT * FROM table2;
If everything goes well, a cloned database will be created. In this newly created database, the table structure and the content will be a replica of the source database.
2.3 Cloning a Database via SQLDump command
Consider a situation where developers want to dump the database objects into a SQL file and later restore it on the same or different server. To achieve this, they can use the below two commands for successfully dumping the database into a SQL file and later restoring it.
Script 2
/* Consider you want to dump the database objects into a SQL file. Developers can exercise the following command - */ /* This command instructs MySQL to log into the MySQL server and exports the DB objects and the data of the given database to a local file. * a. '>' denotes exporting. * b. '--databases' options allows to include both CREATE DATABASE and USE DATABASE in SQL file. */ mysqldump -u <root_username> -p <root_password> --databases <database_name> > <sql_file_location.sql> /* Importing the database */ /* a. '<' denotes importing. */ mysql -u <root_username> -p <root_password> --databases <database_name> < <sql_file_location.sql>
That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
3. Conclusion
In this section, developers learned how to clone an existing database. Developers can download the sample scripts in the Downloads section.
4. Download the SQL Script
This was an example of cloning an existing database.
You can download the full source code of this example here: SQL Clone Database Example
