Back Up And Restore SIngle Table Using SQL Server
Back up and restore a single table with foreign keys using SQL Server Today I needed to backup a single database table, then test a change to the data, then if the change failed, restore the original data. Below is how I did this. What I used I used SQL Server Management Studio to do all of the following steps. I performed the steps on a database running on a SQL 2012 database server. Part 1 – Backup the table SELECT * INTO MyTable_Bak FROM MyTable; Note: This will work usually, however, it won’t work if you have a calculated column. If you have a calculated column, create the table first, then specify the columns you are inserting. I didn’t have a calculated column, so I didn’t take time to figure this out. Part 2 – Restoring the table Step 1 – Finding the Foreign Key Constraints SELECT Name, Object_Name(parent_object_id) as [Table] FROM sys.foreign_keys WHERE referenced_object_id = object_id('MyTable') The results were like this: Name
