I'm having one heck of a time trying to compare and move schemas for a new database. I've tried Red-Gate, ApeXSQL etc. and every time I turn around it's not working. I have a customer who has 5 databases in which all 5 have similar schemas. Some have additional stored procs etc. Let me take a step-back and state that Red-Gate does work, however, because I have 73 schemas to filter by I cannot enter every single schema into Red-Gates filtering. So, I thought about trying SSIS, which I've never used before. My objectives and end goals are outlined below. Note: I'm using Visual Studio 2017 and SSDT for VS 2017
If using SSIS I need a way to skip\continue the process if an object is already present.
Objectives:
* To compare\copy the schemas between Database-1 to Database-2 in which only the 73 pre-determined schemas will be created on Database-2.
* To create the tables, stored procedures, functions etc. on Database-2 but, only the ones that belong to the 73 schemas.
End Goal:
* To have a new database that contains all objects for the 73 pre-determined schemas.
Best Answer
Use Data tier Application to upgrade the database.