Yogesh Chauhan's Blog

How to Create a Backup From Any Database in SQL?

in SQL/MySQL on October 10, 2019

To create a backup we use SELECT INTO statement. Let's look at the details one by one.

SELECT INTO statement creates and copies data into new table. 

To SELECT and copy all columns use the following Syntax:

INTO new_table_name [IN database]
FROM old_table_name
WHERE condition;

[IN database] is only required if the database is external.

To SELECT and copy some columns use the following Syntax:

SELECT column(s)
INTO new_table_name [IN database]
FROM old_table_name
WHERE condition;

Everything will be copies as it is in old table or column(s). Data and datatype as well.

Use the following syntax to create a backup copy:

SELECT * INTO Users_Backup
FROM Users;

If you want to keep multiple backup use the following syntax as well, to save backup into external database.

SELECT * INTO Users_Backup IN 'Another_Backup.mdb'
FROM Users;

If you want to create a backup of usernames and email addresses only then use the following syntax:

SELECT user_name, email INTO Users_Backup IN 'Another_Backup.mdb'
FROM Users;

You can use WHERE condition as well. For example, let's copy all the users from USA into a separate database.

SELECT * INTO USA_Users_Backup IN 'country_users.mdb'
FROM Users
WHERE country='USA';

We can also combine multiple columns from multiple tables. For example,

SELECT Users.email, Orders.quantity
INTO inventory_management
FROM Users
LEFT JOIN Orders ON Users.userID= Orders.OrderID;



Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS? #Aug 1 What is Zdog? #Aug 1 How to add before after image effect using pure CSS? #Jul 31 Add animation to your skills bar using CSS #Jul 31 Use SwiperJS to create mobile touch sliders fast
You might also like these
How to create a CSS full page background image?CSSSelect statement in Postgres with examplesPostgresHow to link/add CSS file to HTML Document?CSSWhat is the difference between Loosely Typed Language and Strongly Typed Language?MiscellaneousHow to create an empty array in Swift?SwiftSQL Right JoinSQL/MySQL