How to backup and restore table
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.
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.
truncate table OriginalTable insert into OriginalTable select * from CopiedTable
Depending on which database you're using, there are faster alternatives.
Have you consdered using a SQL Server unit testing framework such as the open source tSQLt framework?
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.
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.