Scripts Folder option to generate drop scripts?

Osolage

Hello,

I love SQL Compare. It has become exceedingly more useful as newer versions are released!

I would like to see the option to have SQL Compare generate DROP statements at the top of each of the object scripts when generating Script Folders.

Reasoning:

I've been storing SQL scripts for building procedures, functions, tables, views, etc in CVS for years. I have drop and create statements for the object contained in the script file. This allows any developer to do get the latest version of a script out of CVS, modify the code, and then rerun the script to have the object dropped and recreated. Having the script setup this way encourages the developers to modify the script files and commit them to CVS so that we have accurate records of who changed the object, for what reason, and to which JIRA issues the changes apply to.

I find myself using SQL Compare to generate SQL Script folders for comparison to script files in CVS. I do the comparison to audit whether the developers have been thorough in their commits.

The only problem is that the drop scripts are in the CVS version, but not in the scripts that are generated by SQL Compare. So I pretty much have to open every file to do a content comparison. This is rather tedious.

So to summarize, can you guys add an option to SQL Compare that will generate the DROP object command for objects when exporting to Scripts Folders?

I'm happy to help test or further discuss the requirements. Anything to get this useful feature added into the product as I'd greatly benefit from the enhancement.

Thanks so much!

rgribble

I too currently have drop scripts in all of our source controlled SQL scripts, mainly because my homegrown build process runs all of these files against empty staging databases before using RedGate to snapshot the schema, and eventually roll the schema changes to target test/production servers. The reason for the drop scripts is because my simple build process uses a brute force approach where i run a script, and if it fails add it to the end of the list, and rerun it again later... the drops ensure that the scripts dont fail if an item creation succeeded but an alter statement (primary key, permission etc) was the reason for the initial failure

I have recently enhanced my build process to use RedGate SQLCompare (driving the toolkit through c#) to load up the script folder on the left side, comparing with the empty staging DB on the right side, and synchronise all changes into the staging DB. In this way, i can "build" the database schema, and no longer need to worry about what order to do it in etc

From doing this process, i have noticed that RedGate is actually unphased by the DROP statements in the scripts, they still are loaded up fine and RedGate understands the schema objects that should be present.

So one suggestion to allow you to do what you want to (check up on developers), without having to wait for a possible future SQL Compare that can generate drop statements, is to simply do a RedGate comparison of the script folder from source control (complete with DROP statements in them) against the development database, which will identify any changes in the DB that the developers have failed to script out and check in.

This doesnt help with generating scripts if you do want drops in them, but it does help if you want to check up on the development database to try and catch uncommitted changes.

Incidentally you could actually do away with the drop statements in scripts, by having developers use SQL Compare to compare the scripts from source control to their development database, and then synchronise changes into the live DB, thus getting "the latest" version of a table (just as they currently do by running the scripts directly) with the benefit of not losing their test data as a DROP/CREATE would do, because RedGate will do an ALTER where possible



However, I still add my support to this request, to have RedGate (optionally) add drop statements to scripts, for those that want/need it... it would still be a useful feature so I hope they do implement it

Michelle T

I've added this to our feature requests list (SC-3548 for future reference). Thanks for your clear and helpful posts.