How to backup and restore table

Question

During my testing, I want to make a copy of a few tables within the same database before running the tests. After tests are complete, I want to restore the original table with the copy.

What is the best way to do this?
I also want to make sure all indexes and constraints are restored.

DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SELECT @Tablename = 'my_Users'
SELECT @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SELECT @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,' 
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL

EXEC (@SQL) -- Execute SQl statement

How do I restore if I use the above to make a copy.

SQL2005

Solution 1

There are MANY methods to do this, but by far, the simplest is to simply take a backup of the database, work with it, then restore from backup when done. (Instructions here)

Backing up the table is certainly viable, but it's not the easiest method, and once you start working with multiple tables, it gets harder. So rather than address your specific example of restoring a single table, I'm offering general advice on better management of test data.

The safest way of doing this is to NOT restore the original, but rather to not even touch the original. Take a backup of it, and then restore it to a new test server. (Instructions here) Best practices dictate that you should never be doing test or development work on a live database anyway. This is also pretty easy, as well as safe.

Solution 2

Something like:

truncate table OriginalTable
insert into OriginalTable select * from CopiedTable

Depending on which database you're using, there are faster alternatives.

Solution 3

I think the script that I recently used can be useful to somebody.

To backup table you can use next query:

DECLARE @tableName nvarchar(max), @tableName_bck nvarchar(max)
SET @tableName = 'SomeTable';
SET @tableName_bck = 'SomeTable_bck';


-- Backup
DECLARE @insertCommand nvarchar(max)
--SELECT INTO SomeTable_bck FROM SomeTable
SET @insertCommand = 'SELECT * INTO ' + @tableName_bck + ' FROM ' + @tableName
PRINT @insertCommand
EXEC sp_executesql @insertCommand

For restore, because tables often can have IDENTITY fields, you need to SET IDENTITY_INSERT ON and also you need to provide the column list when inserting records. That's why script is a bit more complex:

DECLARE @tableName nvarchar(max), @tableName_bck nvarchar(max)
SET @tableName = 'SomeTable';
SET @tableName_bck = 'SomeTable_bck';

-- Restore
DECLARE @columnList nvarchar(max)
DECLARE @insertCommand nvarchar(max)

SELECT
    @columnList = SUBSTRING(
        (
            SELECT ', ' + column_name AS [text()]
            From INFORMATION_SCHEMA.COLUMNS
            WHERE table_name = @tableName
            ORDER BY table_name
            For XML PATH ('')
        ), 2, 1000);

--INSERT INTO SomeTable(Column1, Column2) SELECT Column1, Column2 FROM SomeTable_bck        
SELECT @insertCommand = 'INSERT INTO ' + @tableName + '(' + @columnList + ') SELECT ' + @columnList + ' FROM ' + @tableName_bck

IF EXISTS (
    SELECT column_name, table_name
    FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'dbo' AND table_name = @tableName
       AND COLUMNPROPERTY(object_id(table_name), column_name, 'IsIdentity') = 1
    )
BEGIN
    SET @insertCommand = 
          'SET IDENTITY_INSERT ' + @tableName + ' ON;' 
        + 'TRUNCATE TABLE ' + @tableName + ';'
        + @insertCommand + ';'
        + 'SET IDENTITY_INSERT ' + @tableName + ' OFF;' 
    /*  
    SET IDENTITY_INSERT SomeTable ON
    TRUNCATE TABLE SomeTable
    INSERT INTO SomeTable(Column1, Column2) SELECT Column1, Column2 FROM SomeTable_bck    
    SET IDENTITY_INSERT SomeTable OFF
    */
END
ELSE
BEGIN
    SET @insertCommand = 
        'TRUNCATE TABLE ' + @tableName + ';'
        + @insertCommand
    /*
    TRUNCATE TABLE SomeTable
    INSERT INTO SomeTable(Column1, Column2) SELECT Column1, Column2 FROM SomeTable_bck     
    */
END 

PRINT @insertCommand
EXEC sp_executesql @insertCommand

It's easy to see, that you can specify @tableName and @tableName_bck however you like it. For example, this can be in a stored procedure, so the script is reusable.

Solution 4

Have you consdered using a SQL Server unit testing framework such as the open source tSQLt framework?

See http://tsqlt.org/

A tSQLt test runs in a transaction so whatever you do within your test will get rolled back.

It has a concept of a "faketable" which is a copy of the original table minus the constraints, if these get in the way of your test setup.

View more solutions

Comments

[Andomar]

A backup cannot restore a single table. Version control stores the definition of a table and not its contents. -1

[David]

@Andomar, you're right, of course. I deleted the version control portion, and edited to make my meaning more clear. I agree with your -1 because I didn't answer his specific question, but I'm leaving it here because I think it's still better general advice than specifically answering his example. Thanks for your feedback!