Temporary backup / restore of data while testing

Question

I need to backup the original data from a table so I can fill it with test data, then restore it again after deving / testing etc.. Anyone know any quick ways to achieve this? I'm on SQL Server 2005.

Thanks.

Answer 1

You could use SSIS to backup the table to a table with a different name in your database.

Then delete the modifed table in the db when you're done, and rename the copied table to the original name. All you need to do then is to recreate the relationships.

EDIT: I would not do this in a production environment

Answer 2

I don't know your specific situation but the norm is it mirror the production server to a dev server and do your tests on that one. Doing destructive tests on production servers is not safe.

There is a lot of documentation on mirroring on MSDN and Technet. A quick google found this article: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Answer 3

Create some temp table. Copy data into temp table. After testing copy data back and delete temp table.