0

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.

  • I don't even have the words for how painful that sounds. I use Visual Studio for schema comparisons; specifically SQL Server Data Tools for Visual Studio. Alternatively, if the types of objects I'm comparing is low, I've written Powershell scripts that give me match, missing, and different for those objects when compared between two databases. The docs on VS diff - docs.microsoft.com/en-us/sql/ssdt/… – Adam Apr 25 at 21:42
-1

Use Data tier Application to upgrade the database.

  • I'm not sure what you mean. The database that I'm moving to is a SQL Server 2017 database. The other databases I could possibly upgrade after restoring on another server however, that probably isn't the best idea. How's upgrading the database going to assist me with SSIS or whatever tool(s) to be used? – Trent Adams Apr 25 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.