How do I backup and restore an individual table?

×

BACKUP:
Use a SELECT INTO query:

SELECT * INTO OldTableNameYYYYMMDD FROM OldTableName

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

RESTORE:
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

RESTORE WITH IDENTITY INSERT:

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
([ord_type]
           ,[ord_no]
           ,[status]
           ,[entered_dt]
           ,[ord_dt]
           ,[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.