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:


SELECT *
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 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
WordPress get_posts ExamplesWordPressWHERE Clause in PostgresPostgresThe flex-grow, flex-shrink and flex-basis Properties in CSSCSSHow to Install PHP Laravel on MacOS Catalina?PHPSequence generator (range) using JavaScript Array.from()JavaScriptWordPress: How to find all posts with a specific custom field value?WordPress