How MERGE few databases to big one

Question

How MERGE few databases to big one

anxcomp

Old Hand

Points: 389
More actions
November 12, 2007 at 8:43 am

#153957

Hi All,
I have to merge few different databases to one big common database 🙂 I mean about structure (tables, procedures, users, roles etc.) and data. May you suggest, help me how I should do this. How start? Create brand new database or relay on one old and add difference from other?
--
Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Hi,

Depending on what your end result may be, you may want to look at something like Red-Gate SQL Compare to help consolidate objects into a single database, but you will need to understand the relationships between databases, as you may need a modified schema.

Thank you,

Phillip Cox

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

We need more info. Are your different databases related, so that there might be common tables and SPs in multiple DBs ? Or are you combining unrelated databases into 1 big one ? And why do you need to do this ?? Is there a compelling business reason ?

anxcomp Old Hand Points: 389 More actions · Answer 3

Yes fortunately each databases are independent (different tables, procedures) but logins are common on every databases, maybe something else.... You asked way I need do this? because I integrate few projects in one, so I'd like use one databases instead four 🙂

Any idea, please 🙂

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 4

Personally I would think you are better off keeping them in seperate databases unless there is some logical relationship between them. However if you do want to merge them then you run into the question "How big are they". And by size I'm talking numbers of objects. Probably the best thing to do is to have a schema or group of schemas per project. But that requires some re-write of at the very least all of your SPs, functions and views. Otherwise you could script out all the objects in each DB, run them into a new DB and then use SSIS or something similar to copy the data.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Kenneth Fisher (11/12/2007)
Personally I would think you are better off keeping them in seperate databases unless there is some logical relationship between them.

I agree. Sounds like a lot of work for no urgent reason.

I suppose it would be fairly easy to use DTS or SSIS to copy all the objects from DB_A to DB_B. But you'll also need to run a search on all your SPs and views to make sure you don't have any hardcoded DB names, such as

select * from DatabaseA.Customer

Do you have DTS packages and jobs that reference the "old" databases ?

anxcomp Old Hand Points: 389 More actions · Answer 6

You are right, I forgot about jobs and references to "old" databases names, I'm sure some procedures also will have hardcoded db names 🙁 I don't have DTS packages.

Is it way to automate this task (some tool), I mean change references?

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 7

Basically the same method. Script it, then search for the DBNames and change them, or since you are putting it all in the same place you can basically just remove them. Do a search on . Make sure you include the period at the end of DBName since 99% of the time in code you are doing a 3 part reference and will have the period at the end. This reduces your chances of false hits.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]