Synchronization

Comments

• david connell Redgate › Posts: 172 Silver 1

  July 26, 2006 11:32AM

  Hi there,
  This SQL Compare will move the schema across, but keeping the existing data intact.
  for example it will add a column to a table and not drop and recreate the table. Sometimes it is necessary to drop and recreate the tables. If this is the case SQL Compare will copy the data into a temporary table and then copy the data back into the newly constructed table.
  Nb. SQL Compare will not copy data between your database schemas. If you want to do that check out SQL Data Compare.
  Hope that helps.
  David

  0 · Share on Twitter
• Brian Donahue Posts: 6,590 Bronze 1

  July 26, 2006 12:02PM

  Hi,
  
  I'd just like to add one thing: be wary if you're in the habit of renaming columns. There is a possibility of data loss because SQL Compare will drop the column from the destination database and create a new one with the new name. It tries as best as it can to preserve the data, but it has no way of knowing yet when a column exists in the second database under a different name.

  0 · Share on Twitter
• cthornburg Posts: 3

  July 26, 2006 4:55PM

  Thanks for the info...I will of course make a backup first...and then move my changes into production.
  
  Thank you!!

  0 · Share on Twitter
• mshiker Posts: 4

  July 27, 2006 11:47AM

  Brian, I'm not clear of the exact situation you are getting at in your post of Wed Jul 26, 2006 10:02 am.
  
  What I'm not clear on is your statement of "but it has no way of knowing yet when a column exists in the second database under a different name". You stated that SQL Compare will drop the old column and create the new one. So, at that point SQL Compare does know that that column now exists. Why would there be an issue with moving the preserved data into that new column?
  
  Thanks,
  Marc

  0 · Share on Twitter
• Brian Donahue Posts: 6,590 Bronze 1

  July 28, 2006 7:26AM

  Suppose you change the name of a column, then run SQL Compare
  
  CREATE TABLE x (
  ID int,
  data varchar(50) <-- Rename to newdata varchar(50) in db2
  )
  
  SQL Compare sees this:
  Database1:
  CREATE TABLE x(
  ID int,
  data varchar(50)
  )
  Database2:
  CREATE TABLE x(
  ID int,
  newdata varchar(50)
  )
  
  SQL Compare generates these actions for db2:
  ALTER TABLE x DROP COLUMN data
  ALTER TABLE x ADD COLUMN newdata varchar(50)
  
  How can SQL Compare know that data and newdata are the same column with a different name?

  0 · Share on Twitter
• mshiker Posts: 4

  July 28, 2006 10:04AM

  Brian,
  
  Thanks for the response. I thought I understood what you were saying but I went and tested it and, I'm sorry, but I still must be missing something. It appears that SQL Compare is able to keep the mapping from renaming the column in either database (db1 or db2).
  
  I first tried renaming the column in db2 (from first_name to new_first_name) then did a SQL Compare. When I view the difference in the SQL Compare UI for that particular table I see the the old column name in db1 and the new column name in db2 highlighted in blue (indicating there was a change and not a drop on one side and an add on the other). In the generated script I can see where the data is inserted into the temp table with both column names in the correct position indicating the correct mapping:
  

  INSERT INTO [dbo].[tmp_rg_xx_Contact]([contact_id], [prefix_id], [first_name], [middle_name], [last_name], [suffix], [preferred_name], [gender_id], [personality_profile_type_id], [ethnicity_id], [birth_date], [birth_month], [file_set_id], [company_id], [create_date], [create_id], [modify_date], [modify_id]) SELECT [contact_id], [prefix_id], [new_first_name], [middle_name], [last_name], [suffix], [preferred_name], [gender_id], [personality_profile_type_id], [ethnicity_id], [birth_date], [birth_month], [file_set_id], [company_id], [create_date], [create_id], [modify_date], [modify_id] FROM [dbo].[Contact]
  

  
  I did not commit that change.
  
  Then I tried renaming the column in db1 (from first_name to new_first_name) and did a SQL Compare. When I view the difference in the SQL Compare UI for that particular table I see the the new column name in db1 and the column name in db2 highlighted in blue (indicating there was a change and not a drop on one side and an add on the other). In the generated script I can see the sp_rename:
  

  PRINT N'Altering [dbo].[Contact]'
  GO
  sp_rename N'[dbo].[Contact].[first_name]', N'new_first_name', 'COLUMN'
  

  
  Are there situations where SQL Compare can do a successful renaming (mapping of old column name to the new name) and there are some more specific situations where I should be concerned?
  
  Thansk again,
  Marc

  0 · Share on Twitter