SQL Schema Compare - Configure to ALTER not DROP/CREATE

Question

I am doing a comparison between two databases in order to generate a change script. However, I noticed that it's consistently creating a temporary table, dropping the old table and then renaming the temporary table to the old table's name. What I want is a direct alter of the existing table. Is there any way to do this?

The reason for this is that our SQL policies for these environments require us to alter what is already there and do not allow us to simply drop & recreate everything.

---

Nathon Dalton
Sr. Software Engineer
Systems Administrator
Network Administrator
Blog: http://nathondalton.wordpress.com

Answer 1

Hello Nathon,

Do these two databases contain data? If so, I think you have to follow CREATE-COPY-DROP-RENAME (CCDR) strategy the avoid data missing during your deployment or schema comparasion. For further information about Managing data motion during your deployments, please see these two blogs:
http://blogs.msdn.com/b/bahill/archive/2009/03/30/managing-data-motion-during-your-deployments-part-1.aspx 
http://blogs.msdn.com/b/bahill/archive/2009/07/02/managing-data-motion-during-your-deployments-part-2.aspx

And if these two databases do not contain any data, if you would like to generate the ALTER script of your schema compare, you can acheive that by exporting the scripts to the editor or file. Go to Data menu->Schema Compare->Export To->Editor|File. With this option, you will get the change script.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

this tool does it right:

http://nobhillsoft.com/NHDBCompare.aspx

its 299$ but it does schema and data in the most efficient way possible