Data Migrator

Use the Data Migrator feature to copy and transform data from one SQL Server database to another. This uses my SqlMigrator component to perform "deep copy" or data cloning operations that involve many steps and foreign key mapping. Data Migrator makes it possible to execute complex migrations, offering robust error handling, retry and resume capability, along with detailed diagnostics. Access the feature under the Tools menu:

The first thing you do is indicate the source and destination connections for your migration. The connections come from your saved list.

Next, you create one or more Steps. A Step contains the parts of an INSERT...SELECT statement that defines the rows you want to copy. When creating a step, you indicate what table in your destination database you're inserting into.

For each Step, you indicate what source data you're selecting FROM along with the column mappings. You must specify the identity columns in the source data as well as the target table. This is what enables SqlMigrator to guarantee that it copies every row exactly once. Your FROM clause can be any valid SQL. Your column mappings may use alias prefixes. For expression columns, prefix the source column with an equal sign as in this example in the IsActive column. For mapped columns -- that is, foreign key columns that get a new value as part of the migration -- fill in the Map From Step column with the name of the table from a prior Step.

Note that your Step FROM clauses may use parameters. In the example above, note that I'm using @libraryId as a parameter. You must declare parameters on the Parameters tab, as shown, and provide a value. This is intended for migrating multi-tenant systems where you need to migrate one tenant at a time.

In the course of a large migration, you will likely try and troubleshoot many SQL insert variations. You can safely test any Step using the Test button. This will attempt to copy 10 rows not tested previously within a transaction that is always rolled back. Any SQL errors will show up, and give you chance to alter your mappings or FROM source as needed. When you're ready to perform an actual copy, use the Run button. To access the underlying SELECT and INSERT statements used, click the respective "Source SQL" and "Insert SQL" links. You can then paste those into your query window of choice to inspect and run as you need to.

Because migrations can take a long time, and errors may lead to frequent interruptions, you can stop a migration at any time. When you start it again, it will automatically skip previously migrated rows.

Moreover, because you'll likely start and stop Steps many times in the course of a large migration, it can help to see the overall progress in terms of the total and remaining (unmapped) number of rows. Use the Progress area in the lower right.