Sql-server – Export subset of data for development environment

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.

I presume you have substantial data volumes if you have separate staging and data warehouse servers. From your posting It sounds like your staging server is doing a bit more than just staging the data. You describe merging data into the fact table on the warehouse server, so I presume that the staging server actually has ETL processing on it as well.

I would not generally have recommended a transform-push approach like this; running ETL processing from the warehouse and pulling from a staging area is generally better. The merge happens on the warehouse. If you need to scale out you can replicate the warehouse, and the same operations will happen on the source and targets.

However, I guess you're stuck with the transform-push architecture, so you've got a couple of options:

• Use SSIS or another ETL tool to copy the incremental load to a staging table on the warehouse and merge it to the fact tables from there. This will be fairly efficient and can be done with set operations - i.e. a merge statement or insert and update.

• Replicate the staging area into another database on your warehouse server and execute the merge from there after the ETL processing on the staging server has run. This is probably more fiddly than the first option, and I can't really see any advantages to it.

• If your staging and warehouse databases are located on the same database instance then you can create synonyms in the warehouse and merge through the synonyms. The synonyms prevent a need to hard-code database refererences.