Quickest way to roll back SQL data Was: Best way to develop a data-mangling stored procedure

Question

Edit: OK I asked the wrong question here.

I'm going to be coding a stored proc that affects a lot of data, so I need to know the quickest, easiest way to roll back the data to the original state after I run a test.

---

Old question: I have a development database holding live data. This needs to be obfuscated for privacy, particularly company names and contact details.

To throw a spanner in the works the company NAME is the primary key. (.... yes, i know. legacy code. hooray.)

Now, I need to obfuscate the company name (say, change each to "Company 001" etc.) while preserving referential integrity with dozens of tables linked by this value. During my testing I'm going to mangle a lot of data, and then need to roll back to the original state after testing, probably many times before i get the procedure correct.

So the process will be:

• Mangle company data
• test within the application to ensure linked data displays correctly
• roll back data for bugfixes repeat

My initial thought is to simply back up and restore after each test. But this seems time consuming. Is there a better way?

Answer 1

IMHO mangling data inside your database is going to do little more than muck up your database. If prying eyes have gained access to your database I fear you have bigger problems on your hands at worst and at best they'll figure out how to undo the mangling. They did, hypothetically after all, just gain access to your database. Reversing some obfuscation will be the easy part.

Answer 2

Use Test database with dummy data if that is available. Blow it away and repopulate with more random dummy data. If you already have fixtures for this then you are most of the way there. If you don't this is a good way to get started writing them.

Other than that backup and restore is probably your best bet.

Answer 3

If you are doing data tests of some kind, please considering using a test database or a playground database for this...

If that is not possible... The code below will rollback all the data changes...

BEGIN TRANSACTION
--do my tests
ROLLBACK

EDIT
You could also add some code into your application that will perform the tests and then restore a backup after your test is complete.

Answer 4

Options I've discovered so far:

Microsoft SQL Server Database Publishing Wizard 1.1 for MS SQL 2005

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

(one hyperlink limit)

Answer 5

If the database has referential integrity enabled via foreign key constraints that aren't setup to auto update on change then the commit should fail.

You could update the constraints to auto change on update so when you update the parent record, the child records are then updated automatically.