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
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.