How to select a subset of data from a large ER model?

Question

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from all tables so that I don't break referential integrity and get orphanded records from the database? I mean that if I choose 100 records e.g. From the "product" table, how can I easily get all the data from the referenced tables (and from the tables these tables are referenced)? Is there any tool availble for this purpose or just pure SQL?

Answer 1

Unfortunately there isn't a cookie-cutter way to do this. You have to start with some base records, write queries that join against them and then repeat. As you go deeper the queries get more complex as they have to go right back to the root.

If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns. It's not as hard as it sounds, but here are a few pointers if you take this approach:

• The queries are only going to retrieve the contents of the table at their leaf, so they are fairly straightforward to generate.

• Get the data out in a form that will load through bcp. As a bonus, you can generate bcp control files from the system metadata.

• You can probably quite safely just copy all of the reference data, which probably accounts for the majority of your tables anyway.

• You may have to start from multiple roots, and you may have to manually add join metadata if you have missing foreign keys. If your relationships to your leaf tables are not purely hierarchical then you may have to manually edit the metadata for the generator. In practice this means you probably want to start from the system data dictionary and then make up a configuration file for your generator that works around this.

• You may also need to add custom queries in for certain data items if the selection criteria involve more than one path to the record.

• Generate or write a script that uses bcp to load the tables to the destination in the right order.

Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write. It's not a 5 minute job, but it's certainly possible.

You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier. Another option would be to just structure the scripts so they copy the data from the queries into another shadow database. This can then be backed-up/restored to wherever you want.

It's not a trivial undertaking but it's certainly not beyond the wit of man. As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database. This will be quite useful for rolling out test environments.

Answer 2

The strategy would depend on the design of the schema.

You could take the first 1000 rows from each table and go from there. If you have constraints/keys/etc. that you cannot break then you need to identify the data to use. One way to do this is to create a list of key values from one of the tables and then select out from the other tables using a filter like:

WHERE <key value> IN "<key value list>"

or you could just use a subquery to replace the "<key value list>"

Another alternative is the make a copy of your database then delete a bunch of data (is there an associated application to the db that has a suitable delete function?) It may just be easier to recreate some data - especially if the database has an application attached to it.

Answer 3

Here at Redgate we're building SQL Clone, a solution that solves this problem but avoids the need to subset the data, as it uses virtualisation tech to provide access to the full data set for dev/test environments.

SQL Clone is still in preview and we're looking for feedback via an Early Access Program to know if this satisfies the database provisioning problem. If you have the problem and are able to help, please sign up!