Help with best practice of merging databases

Posted on

Question :

Bad news, our website server (Windows 2003) crashed because of dead RAID controller.

Luckily few hours later our backup server was up and website was live again.

Hopefully tomorrow our original server will be fixed and I’m looking for the best practice to merge our data (SQL Server 2005).

This is the situation right now:

  • Original server has backed up data till 16th 01:30AM (night.bak)
  • Original server has data which is not backed up till 17th 00:30AM
    so basically there is 23 hours missing on the night.bak

I restored the night.bak to the temporary server and since 17th 03:00 new data is being added to the temporary server.

Tomorrow I wish to take data from the temporary server (17th 03:00 till 19th) and put it back on the original server.

I believe I can’t make differential backup on the temporary server and restore it on the original server because last backup on original server has timestamp of 16th 01:30AM but I don’t really know so I’m asking here.

My main concern is to preserve data of joined tables that share index keys.

I don’t know how to reply to the answer so I’m replying here :

I’m testing Red-Gate’s SQL Data Compare (which is by the way 14 days trial and not 30) and it seems good to add / update / delete rows very easily.

But this is the problem:

  • old database has 120 records on tableA (primary key 1-120) when I left it
  • new temp database doesn’t have all 120 records because only 100 records were backed up so, it has only 100 records (primary key 1-100)

Since we continue using the temp database it now has 140 records (primary key 1-100 from original database and 101-140 from temp database).

The Compare would want to insert 40 new records to the tableA but it cannot use 101-120 key because it’s already exists on the original database so I guess it will try to update and destroy them.

And anyway, it cannot insert with correct keys because, for example, I have table tbl_users (code(index), firstname, email) and table tbl_priceoffers (code(index), usercode(from tbl_users), price).

Red-Gate generate script to insert tbl_priceoffers before tbl_users
but even if it was the correct order (I can edit), it cannot insert tbl_priceoffers row without having @@identity from the recently inserted tbl_users row.

Answer :

Your best bet is to use Redgate’s data compare to compare and insert data in the database your application is pointing to.

I have used this method in similiar situation and trust me … this tool is a life saver.

You can dowload a 30 day full trial version and then purchase it if it suits your needs.

Leave a Reply

Your email address will not be published. Required fields are marked *