SCO Drops All Objects in Target if Pointed to an Empty Source Directory

mbruegel

I've noticed that Schema Compare command-line has a nasty "feature" that will drop all objects from the target even if the source folder (SchemaName) location is completely empty (i.e. has neither sub-folders, nor the schemainformation.xml file).

We're trying to get RedGate SCO as part of an automated deployment, using the command line version of SCO.  We discovered this behavior while testing (when we inadvertently failed to fetch the source script correctly and purged our demo target!).  Since then we have incorporated a sanity check into our deployment script (to check for at least one sub-folder).

Can't SCO include some sort of sanity check / protection to guard against pointing to an empty source (schema name) folder?  (an override or force flag could be added if this behavior is desirable)

David Atkinson

The sub folder option isn't ideal in case we're reading a flat file structure (not created with RG tools). I'd be tempted to special case the zero objects case for the scripts folder. I'll discuss this with the team. The only risk to this is that we break this for users who use this technique to empty out a schema of objects! 

David Atkinson

Thanks for the feedback. The reason we don't require a schemainformation.xml file is that there are some legitimate use cases where you might want to read in state scripts that haven't been created using a Redgate tool. But I'm struggling to think of a use case where I'd want to have an empty source, so maybe we could special case this and fail when no objects have been found in the scripts folder source?

An alternative would be to use the /abortonwarnings:high switch, which would prevent a deployment where there was data loss. But this is a blunt instrument that will also fail if other high warnings are detected. 

mbruegel

Yeah the /abortwarnings is too blunt as columns or tables may be legitimately dropped as part of a deployment.

That said there should be some safeguards against nuking your entire schema!  (if you really want to fine, but it should be explicit -- like a purge target option)

I also note that the source script folder doesn't even need to exist -- specify FOO as the schema name (where that folder doesn't even exist) and the target gets purged as well!

Sanity check could be -- the standard sub-folders under the schema (at least check for one of them) -- otherwise an empty source it is in effect a purge all objects operation.