Home » Core Java » sql » SQL Clone Database Example

About Yatin

Yatin
The author is graduated in Electronics & Telecommunication. During his studies, he has been involved with a significant number of projects ranging from programming and software engineering to telecommunications analysis. He works as a technical lead in the information technology sector where he is primarily involved with projects based on Java/J2EE technologies platform and novel UI technologies.

SQL Clone Database Example

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.

Fig. 1: Database & Table Creation

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.

Fig. 2: Cloned Database & Table

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.

Download
You can download the full source code of this example here: SQL Clone Database Example
(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of