I'm developing an application that is using SQL Server for storage. For development/testing purposes I need a subset of production data that will be anonymised, converted into insert into table.. statements and checked into version control.

So far I've been doing

select top 100 * 
into tempTableName
from TableName

then removing any personal data:

update tempTableName set 
firstname = 'Joe',
Surname = 'Doe',
Email = 'joe@doe.com',

Then generating scripts with data and tick only tempTableName.

That takes a fair chunk of time and error-prone - especially the bit where I remove personal data (think what would happen if I put wrong table name into the update).

Is there a better way to get sample data from production into scripts?

  • How will you handle table to table dependencies (parent,child) or other relationships? Picking top 100 from one table may not work if you don't pick ALL corresponding rows from dependent tables. When i have had to do this type of thing, I try to find the 'highest' level in the relationship. Maybe something like top 100 accounts or even better to use some kind of modulus query so you get a much more random sample. Insert those into a temp table and then write your extracts so they work against the top level extracted data joining appropriately - if that makes sense. – Scott Hodgin Nov 25 '16 at 12:49
  • Can you also tell us if you have a different DB server for development/testing? Or you want to sample data into production box itself? If yes, have you considered having different database created for the same activity with different schema? – SwapnilBhate Nov 25 '16 at 12:52
  • @SwapnilBhate Yes, versions of SQL Server are different - I have SQLExpress for dev, LocalDB to run tests against. But production is 2008. Schema is simple enough that all these versions of SQL Server support the schema. Schema is not identical: prod has hundreds of tables, but I'm working only with a handful of them. So my dev schema is only a subset of prod. – trailmax Nov 25 '16 at 13:07
  • @SwapnilBhate indeed, I need to start from the top, but that's what I'm doing anyway, but picking only a handful of "child" tables - only the ones I'm working with – trailmax Nov 25 '16 at 13:09
  • @trailmax have you considered using import/export wizard to migrate the selective data? – SwapnilBhate Dec 2 '16 at 12:28

I've use the script in this post: Updating multiple rows with random values from another table https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f58c3bf8-e6b7-4cf5-9466-7027164afdc0/updating-multiple-rows-with-random-values-from-another-table?forum=sqlgetstarted

Generate the random data (another table) first: http://www.generatedata.com/

| improve this answer | |
  • 1
    random data is the least of the problem. Mostly getting the data out in (semi)automated way is the issue here – trailmax Nov 30 '16 at 11:19

What we have understood here is you want to migrate selective data from selective tables from SQL 2008 server into SQL express edition.

Have you considered using the import/export data feature on the SQL management studio? Import/Export wizard can be accessed by logging into server using the SQL Management Studio. Here is the link to MSDN library.

You can select the source as SQL 2008 server (production) and destination as SQL Express Edition (development). And then map the required tables and columns. If required, truncate the tables from the development environment first if you want to have a clean data.

Once data is migrated, you can perform the update queries to remove/mask any personal or confidential data.

Let me know if this serves the purpose else we will delete the answer later and will think something else.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.