We have a strange scenario.

We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2.

The source database table has 5 columns. The target database contains an extra column which is not nullable and has a DEFAULT value defined.

We need to push data for the 5 columns from source to target. If we use any tool like red-gate or dbcompare, it will compare source with destination and provide a query to insert the new data on to the target, however running the query fails as it is not inserting anything into the newly introduced column which is not nullable.

Any idea on how to handle this?

  • 1
    If the comparison tool does not include the extra column in the generated INSERT statement(s), and there is a default on that column, where is the error message during deployment coming from? – Darin Strait Apr 17 '12 at 12:18
  • 1
    Redgate has two separate tools, one for data (DML) compare, and the regular compare tool is for schema (DDL). To "check the schema difference" use the regular compare tool. – Eric Higgins Apr 17 '12 at 19:15
  • We modified the generated scripts and we move ahead. Thanks for all of the help. – Rampo Mar 21 '13 at 13:13

Did you

  1. Copied all the data to SQL2008
  2. Updated the schema on SQL2008 according to the upgrade script?

Copy data shouln't be the problem here, schema compare neither ;)

For the scheme compare from sql 2005 to 2008 you may use the free Open DbDiff from http://opendbiff.codeplex.com and apply the resulting script to sql 2008.

Otherwise use the new version of the SQL Server Data Tools http://msdn.microsoft.com/en-us/data/tools.aspx that allows SQL 2005 to 2008/2012/Azure migration and scheme update out of Visual Studio 2010+

| improve this answer | |
  • We modified the generated scripts and we move ahead. Thanks for all of the help. – Rampo Mar 21 '13 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.