Depoly changes to structure

[anorthcote]

Hi,

I am dealing with the SQL side of a Visual Studio project that is being developed further. I have copied the existing live database to a development server to build the new structure.

What I need is to be able to script the changes to the structure to deploy them to the live server when needed without effecting the existing data.

I know I can script the entire database but this will either drop the existing tables - thus deleting the live data or, by specifing no to 'drop tables' will try and create them, but if they already exist won't this fail?

I need a way of doing this without buying third party products like 'SQLCompare' or 'SQL Change Manager'

Can anyone help?

 

[SQLSister]

Buy SQL Compare, you will more than make back the cost on the first deployment. You could write a script to check the structure between both databases and highlight which tables are changed (there are always changes the developers don't remember to tell you about). Then create alter table statments to do the differnences. And of course you will need to check constraints and foreign leys and indexes and stored procs and userdefined functions, etc. Truly you are far better off spending the money, you will spend more than it costs in development time to do this every time you deploy.

"NOTHING is more important in a database than integrity." ESquared

 

[RiverGuy]

Copy data to temp tables.
Drop and recreate objects.
Copy data from temp tables to new tables.
Delete temp tables.