SQL Server: How to Backup Data from Tables (Table1 & Table2)
To create a backup of data from existing tables (table1 and table2),
You can use two common SQL methods: SELECT INTO or INSERT INTO.
The choice depends on whether the backup tables already exist or not.
1. Using SELECT INTO – Quick Backup with Table Creation
This method creates a new table and directly copies data from the source table.
SELECT * INTO table1_Backup FROM table1;
SELECT * INTO table2_Backup FROM table2;
- Note: This will fail if
table1_Backup or table2_Backup already exist.
- Ideal for one-time backups or quick cloning.
2. Using INSERT INTO – Backup into Existing Tables
Use this method when the backup tables are already created (i.e., the schema is predefined).
INSERT INTO table1_Backup SELECT * FROM table1;
INSERT INTO table2_Backup SELECT * FROM table2;
- No error if backup tables already exist.
- Useful for regular/daily backups where the structure is fixed.
Summary
| Method |
Creates Backup Table? |
Use Case |
SELECT INTO |
Yes |
First-time backup / fast duplication |
INSERT INTO |
No (table must exist) |
Repeated backups / controlled schema |