March 29, 2014 at 4:59 pm
I have two tables, both which have have an auto-generated primary key column.
The second table is a child to the first, so it has a FK to the parent table.
For instance:
Table A
--------
table_a_auto-gen_id (auto generated primary key)
customer_first_name
customer_last_name
customer_dob
Table B
--------
table_b_auto_gen_id (auto generated primary key)
table_a_auto_gen_id
customer_phone
I want to back up both tables in case I need to restore them the next day (only these tables would need to be backed up, not the complete database).
What is the best way to accomplish this?
I could do a simple select * into table_a_backup from table_a, select * into table_b_backup from table_b. I could then delete the data from table_a and table_b and insert from the backup tables, but that would be a problem with the primary keys.
I could also:
- script table_a and table_b to new tables ahead of time (with the same primary and foreign keys)
- SET IDENTITY_INSERT ON for the new backup tables backup_table_a, backup_table_b
- insert data from table_a to backup_table_a, table_b to backup_table_b
- SET IDENTITY_INSERT OFF
- if necessary, drop table_a and table_b and then rename backup_table_a to table_a and backup_table_b to table_b.
Is there an easier way than the above approach? Are there problem with the above approach?
Thanks.
March 30, 2014 at 11:32 am
You can just do a "select * into" to create the backup tables then script the PK and FK and create them on the backup tables. If you need to restore the tables, rename the originals to tableA_old, rename the backup to the original name and then drop tableA_old.
March 30, 2014 at 12:10 pm
Spork (3/29/2014)
...I want to back up both tables in case I need to restore them the next day (only these tables would need to be backed up, not the complete database).
...
Please tell me that you still have appropriately scheduled backups of your database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply