TSQL Fast Back Up Restore of Table

One of our products was not designed to be disabled easily. There are several tables connected to each other which limit a specific type of functionality. To come up with a plan for how to "disable"/"reenable" this feature quickly, and, without error, I came up with the following plan. In my case, I am simply touching one key table with contains dummy data for my example here, but, which, in the real application determines which users do or do not have the designated functionality.

So, in our fake application I have a table called testing.dbo.will which can be created with this command:

USE [Testing]

GO

/****** Object:  Table [dbo].[will]    Script Date: 06/29/2012 15:00:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[will](

      [GroupID] [int] NOT NULL,

      [UserID] [int] NOT NULL

) ON [PRIMARY]

GO

To add data use this command:

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (1, 1)

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (1, 2)

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (1, 3)

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (2, 1)

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (2, 1)

INSERT INTO [Testing].[dbo].[Will] (GroupID, UserID) VALUES (2, 1)

Now that we have a sample table, we can create a new table AND copy table from this original table with this command:

SELECT *

INTO [testing].[dbo].[new]

FROM [testing].[dbo].[will]

To verify we have an exact match I wrote this command to compare the two tables. Any results will indicate records that are not identical. Ideally, there will be none.

SELECT MIN(TableName) as TableName, GroupId, UserID

FROM

(

  SELECT Table A as TableName, Orig.*

  FROM testing.dbo.will AS Orig

  UNION ALL

  SELECT Table B as TableName, Archive.*

  FROM testing.dbo.new AS Archive

) tmp

GROUP BY GroupId,UserID

HAVING COUNT(*) = 1

Once I am positive I have an exact match (i.e., there are no records when I run the above query) I will simply remove records from the original table:

DELETE
FROM [Testing].[dbo].[Will]

This way, we have a clean slate and our users are now able to do anything they choose. So, to restore users, we use this approach:

INSERT INTO [Testing].[dbo].[will]

SELECT *

FROM [Testing].[dbo].[New]

Again, a quick sanity check to identify and potential misses:

SELECT MIN(TableName) as TableName, GroupId, UserID

FROM

(

  SELECT Table A as TableName, Orig.*

  FROM testing.dbo.will AS Orig

  UNION ALL

  SELECT Table B as TableName, Archive.*

  FROM testing.dbo.new AS Archive

) tmp

GROUP BY GroupId, UserID

HAVING COUNT(*) = 1

And, a final clean up sweep:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N[Testing].[dbo].[will]) AND type in (NU))

DROP TABLE [Testing].[dbo].[will]

GO

Related Post: