How to Create a Backup From Any Database in SQL?

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;

Share this post