How do I backup and restore an individual table?


Use a SELECT INTO query:


For example, if you are working on the Customers table and want to make a quick backup before continuing, run the following query:

SELECT * INTO Customers20120605 FROM Customers

Note - Using the format of YYYYMMDD at the end of the table allows the BirdDog Job Master Purge Table Job to automatically remove the table based upon your business rules

Use an INSERT INTO query:

Note: Truncate original table prior to restoring with the backed up data.
TRUNCATE TABLE OriginalTableName

INSERT INTO OriginalTableName
Select * from BackedUpTableName


Some tables will throw an error like this if you run the above restore query: An explicit value for the identity column in table 'OEORDHDR_SQL' can only be specified when a column list is used and IDENTITY_INSERT is ON. In this case, you will need to take some additional steps.

First, in SQL navigate to the OriginalTableName, then right-click on it and select Script Table as | INSERT To New Query. THis will generate a new query with INSERT INTO [dbo].[OriginalTableName] followed by lots of [column_name]. You'll want to copy this info, then use the following query (in this example, I'm using OEORDHDR_SQL):

SET identity_insert OriginalTableName ON
insert into OriginalTableName
           ,[and so on])
select * from BackedUpTableName
SET identity_insert OriginalTableName OFF

Note: Sometimes SQL does not include the final [identity] column so it will need to be manually added at the end as the ,[identity]) for this query to work correctly.