After I migrated some database from SQL 2000 to SQL 2012 (via SQL2008r2) I was unable to get the front-end applications switched to point to the new backend straight away.

This resulted in the unused SQL 2012 databases gradually becoming out-of-date.

To update them without the pain of repeating the migration I found that I could use the SQL 2012 Import Wizard.

There were three tricky bits to this …

1) to use [Microsoft OLE DB Provider for SQL Server] to connect to the SQL 2000 server
2) to select all Tables but not Views, and
3) To edit the mappings for each table choosing the radio-buttons to [Delete rows in destination Table] and [Enable identity insert]

After this it was simply a matter of saving the output SSIS packages to jobs.

** Update ** … or so I thought. This failed for one database that contained Foreign-keys. So I had to create a ‘pre update’ step to remove the constraints, then a ‘post update’ step to put them back (see next).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s