Generate upgrade script at install-time

[j]

Hi there,

I'm interested in using a product that allows me to specify a target schema (and minimum data requirements) as part of an installation.

At install time, if the database doesn't exist, it is created. I have figured out how redgate takes us that far.

BUT, If the database ALREADY exists, I'd like the installer to create a custom change script right then, specific to that existing database, for the changes necessary to ensure that the target schema is as it ought to be.

The installer should attempt to build the change script, show any warnings of incompatible constraints, data loss, etc.

As a bonus, the installer might be tolerant of additional schema objects present in the existing database and leave them be (optionally), as long as the objects don't conflict with the target schema and minimum data.

SQL Packager seems to have all of the necessary technology to do this, but I haven't found it as an option.

Is it possible with redgate tools? Is there a very good technical reason why it can't/shouldn't be done this way?

Thanks,

J

[simon.jackson]

Thanks for your interest. We are aware that this is a limitation of SQL Packager, and it may be something we look at in the future. It might be worth pointing out that if you know the state of the remote database you can use Packager to create an upgrade script, but this is obviously not always appropriate.

It is, however, currently possible to solve this problem using Red Gate technology, if you're prepared to get your hands dirty and write some C#, VB or other .NET code. The relevant product is the SQL Comparison SDK, which gives you full API access to Red Gate comparison tools.

In particular if you look at the Compare API samples "LoadAndSaveASnapshot" and "SynchronizeDatabases" you should be able to ship a SQL Compare snapshot and use this to create an upgrade script at the customer site.

You can even leave additional objects in the target schema by unsetting the SessionSettings.IncludeRecordsInTwo option!

Hope that helps

[j]

Thank you for the response!

That is a very reasonable option. Which DLLs are redistributable under the SQL Compare license? How does licensing work for those distributed assemblies - is there an end user key distributed along with my software?

Thanks,

J

[j]

Nevermind, I found this information on your website. Thanks!

[luckystar09]

Thanks for your posts. They are really very cool, very wonderful
script install