0

I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different databases). Besides there are some SSIS packages that point to the different databases when they load data.

I think people who created the databases thought the a database is a folder and they made thier own system (e.g. "let us put these tables in a new database"). All objects are in the dbo schema and the names do not collude. I don't see any benefit of having multiple DBs. But many benefits of having one. E.g. I cannot create indexed views and objects from different databases are involved. I have to monitor the SPs from different databses, etc.

Is there any smart way of mergin the databases, or does everything have to be done manually? Is there a tool, e.g. on Redgate? So everything is on the same instance... Just some tips... Thanks.

1

I've always found SQL Examiner to be a great tool that's super easy to use when synchronizing one database from another.

Since you said the names don't collide between entities, then SQL Examiner will basically create all the entities (which you get to choose, e.g. you can exclude all procedures, or hand pick which views, etc) in the destination database from the source database.

It also has features such as showing you the scripts it generates before executing them, creating backups before making changes, and is just an overall good "comparison" tool for both schema and data differences between two databases.

They give you a 30 day full featured free trial, so you can test it out for a while first before deciding to buy.

Additionally you can check out ApexSQL's SQL Compare.

2
  • Hi J.D. Are there any other tools? Does Redgate have a tool for these things too?
    – xhr489
    Feb 11 at 13:18
  • @xhr489 Looks like RedGate has one as well (not sure if their trial is fully functional or not) per MBuschi's answer. ApexSQL's SQL Compare is another one, I'll add it to my answer. But I've found out of all of them, SQL Examiner to be the best mix of simplicity but having everything one would need for database migrations.
    – J.D.
    Feb 11 at 15:16
1

You can also generate a dacpac from one of them and the generate script of differences with the second one.

https://docs.microsoft.com/it-it/sql/tools/sqlpackage/sqlpackage?redirectedfrom=MSDN&view=sql-server-ver15

It is not as easy as a tool but it is free.

There is also Redgate SQL Compare:

https://www.red-gate.com/products/sql-development/sql-compare/

3
  • Hi thanks, just looked at it. Do you mean first extract the DB and then import it to a new the new one? I don't need to compare anything just move all tables and SP and don't want any code to break since there are cross database dependencies..
    – xhr489
    Feb 11 at 13:59
  • I mean that all these tools give you an overview of the differences, what is missing or what is different and you have to confirm what you want to sync to the target.
    – MBuschi
    Feb 11 at 14:46
  • "I don't want any code to break since there are cross database dependencies" you will have to update code that is currently pointing at the other database. No way around that.
    – Bee_Riii
    Feb 11 at 15:15

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.