Merge two or more different database backup(.bak) files into one new database

Dear Team,

I have to merge two or more .bak backup files into one database. Could you please provide me the solution for my requirement.

Example:

Two DB backups: Test1.bak & Test2.bak

Merge these two DB's into One Db called "Test".

More info please.

Present I have two databases in SQL server 2014. I.e. DB1 & DB2

I want to merge those two DB's into one final DB. I.e. MergeDB

Regards,
Prasad

Are the tables in each DB unique?

1 Like

Yes.
Example:

DB1 Tables are: Student, Teacher

DB2 Tables are: Orders, Customers

I need to merge DB1 & DB2

Expected DB Details
MergeDB tables: Student, Teacher, Orders, Customers

Ok. You need to script the table defs and data movement. Import export wizard would be easy way.

Do you definitely need to merge the two databases into one, or could you reference one-from-the-other?

For example, you could create a SYNONYM in DB1 for each table in DB2 and then, when connected to DB1, you can query both Student, Teacher tables and also Orders, Customers - as if they were in the same database.

That said, if there is no reason to segregate them then having them in one database provides some benefits - for example, a RESTORE of the database is to a point-in-time where all the data was in sync, whereas with two databases they could be restored to different points-in-time :frowning:

I suppose I should ask: What's the reason for wanting to combine them?

We can't do two DB's into one DB while using import and export wizard.
Regards,
Prasad

  1. Restore each to their individual databases
  2. Via tsql script or ssis packages or powershell move data to final destination database

There are two ways: natively – using relatively complex queries that will take ton of time or using third party tools which in this case I would recommend, because there are lots of them on the market like http://www.sqlrecoverytool.com/ which exports the database on an existing database which will help you in merging two databases into one.

For this, first of all you need to restore both the .bak files on SQL Server.

I have tried to restore two databases .bak files into destination DB. But
in this senior I am getting errors why because these two DB's are Unique
DB's.

of course you can! Have you tried?

Yes.. I have tried but didn't worked out😑

can you be more specific? I really believe this is the way to go. talk about what you did, and what didn't work out. be specific.

You cannot RESTORE database 1 - and then RESTORE database 2 over that and get a combined result. What you need to do is restore database 1 as db1 - restore database 2 as db2 and then:

  • Pick a database that will contain the final results (say db1)
  • Script the objects from the 'other (db2)' database and create those objects in the 'final (db1)' database. This will require scripting everything (tables, views, procedures, functions, users, schema, synonym, etc...).
  • Once all objects from 'other (db2)' have been created in 'final (db1)' - then you can copy the data using T-SQL, SSIS, Import/Export Wizard, BCP, Powershell, etc...

When scripting out the create statements - you need to insure that all parts of the object are scripted. For Tables that means you need to include in the script the indexes, constraints, defaults, etc... This can be controlled in SSMS Options which usually has these additional items disabled by default.

Dear Williams,

This solution I already know. But problem here is, my DB2 having more than
500 tables. So script the objects from DB2 is not possible because it will
take huge time and also not accurate, why because tables having more than
100 columns. So I am requesting you please provide me other solution for
this.

Regards,
Prasad

What you are asking for is "magic".

Either stick with two databases, or do all the (yes, I agree, its a "lot of work") Scripting, data Transferring, and then you will need a full QA Test of the whole system and all APPs that connect.

Please don't be fooled into doing simple SELECT * INTO DB2.MyTable FROM DB1.MyTable because you won't get Indexes, FKeys, nor all the other important constraints etc., you also won't get SProcs, Views or Triggers, and so on and such forth.

Its a major job to combine two databases (if they are of "some complexity"), so you may be better off sticking with two databases.

As I asked earlier:

Dear Kristen,

My requirement is have to merge two DB's into One DB.

Do you definitely need to merge the two databases into one ? Yes

Regards,
Prasad

OK, then your only route is scripting and data transfer which is quite a palaver ...

The only other option - which may or may not even work - is to download SQL Compare and SQL Data Compare from Redgate (or similar tools from any other vendor).

Restore DB1 and DB2...pick one of the databases to be the 'final' database (e.g. DB1) - then perform a schema comparison (SQL Compare) between DB1 and DB2. The SQL Compare utility will then compare all objects in both databases and build a script to 'migrate' the objects from one database to the other.

Once you have all objects created in DB1 from DB2 - you then run a comparison using SQL Data Compare and select all of the objects created in the first step. This will identify all of the data in DB2 that needs to be migrated and build the scripts to perform that migration...

Where this process usually fails is when the tables are not tables - but are heaps...and where there are no primary keys defined on the tables...but you would have the same issues when scripting yourself so this might be quicker...

And - if you have any objects that have the same schema and name in both databases...you have to decide which one you want to keep...

Just be aware that you could end up spending more time looking for an 'easier' solution that it would actually take to do the work in the first place. At some point you have to decide that you have tried everything else and you have spent enough time on it - and then just start scripting out everything.

2 Likes