At my company we have a need to migrate a portion of our provisional data between versions of our database, housed on different servers (upserts for the most part). For release management purposes, we have several environments, each containing a slightly different version of our database. The differences may include additional or missing: columns, constraints, tables, views, etc. Most importantly, we always have different data (identities representing the same entities may differ across environments as well).

The purpose of our migration requirement is that we'd like to be able to copy data back and forth between environments to stage production changes in our UAT environment, or to copy a particular data scenario/configuration back to our development environment for debugging or testing purposes.

I've read that SSIS is an option, but I'm not sure this applies to my scenario since we would like to leave existing similar (but unrelated) data in-tact. So far we've achieved our goal via custom migration scripts which use dynamically generated merge statements and by tracking identity values to maintain relationships. This approach is limited in capability and, without regular maintenance, it quickly falls out of date.

It seems our only viable alternative is spinning up a new SQL instance per configuration, which is an option not well-received by management (due to licensing and maintenance costs primarily).

Is there an easier/lower-maintenance way to transfer only a portion of our data dynamically between environments, while leaving similar but unrelated data in-tact? Or are we approaching the problem incorrectly?

  • Can't move .mdf files? Or detach and attach? – McNets Mar 26 at 13:44
  • @McNets Thanks for the suggestion, but this wouldn't satisfy the requirement to preserve existing data in the destination environment. I'd like to upsert particular data. – alan Mar 26 at 14:10
  • 1
    I use SSIS to export some tables to my data warehouse, but for the whole database maybe a log shipping solution is more situable. – McNets Mar 26 at 14:13
  • 1
    @alan the risk is that If you have unique ids like acct numbers etc you'd need to give them new values to avoid duplicates. Child rows would then need to refer to this new value as they are migrated. Its possible but complex. Ssis is no better at this than sql. – Sir Swears-a-lot Mar 27 at 9:17
  • 1
    If you have the disk space you could restore a backup to another server and give the db a diferent name. You dont need a new instance license. – Sir Swears-a-lot Mar 27 at 9:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.