I've downloaded the trial edition of SQL Compare to assess whether it will meet our requirements, but I've hit a problem right at the start!
I have an old database that was created with all objects in the default [dbo] schema, and a new structure that has moved them into new individual schemas, e.g.
- Synonym1 (dbo.Table1 -> Schema1.Table1)
Synonym2 (dbo.Table2 -> Schema2.Table2)
First of all, if I have an existing OldDB that I want to update to the NewDB structure, what is the source and what is the target? This nomenclature is ambiguous - my existing OldDB is the target of the update operation that I want to perform, but the goal is to end up with a database updated to look like NewDB, so NewDB is also technically "the target".
Either way, it doesn't appear to be possible to specify multiple mappings to a single schema. Given the above scenario, I would want to end up with the following:
OldDB.dbo.Table1 -> NewDB.Schema1.Table1
OldDB.dbo.Table2 -> NewDB.Schema2.Table2
so in either direction I would need a 1-to-many mapping for schemas.
I accepted the defaults and ran the comparison anyway, assuming that I would be able to manually map objects when the comparison was complete, but the online help appears to suggest that all I can do is include/exclude the matches that the compare has found, rather than being able to specify my own.
Can someone please confirm for me whether this is indeed the case, or have I misunderstood something fundamental from the get-go?