SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with best practice for merging 2 sql databases


help with best practice for merging 2 sql databases

Author
Message
crashoverrride
crashoverrride
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
Bad news, our website server crashed...
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 SQL data.

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 data till 17th 00:30AM
so basically there is 23 hours missing on the night.bak

I restored the night.bak to the temporary server 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

Thanks for the helpers!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220825 Visits: 46279
Something like Red Gate's SQL Data Compare would work well here. Otherwise you're going to be writing comparison and insert/update queries for quite some time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


crashoverrride
crashoverrride
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
thank you
i'm testing redgate's 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 was 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 sql 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)

redgate 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


any thoughts ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220825 Visits: 46279
Sync table by table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


crashoverrride
crashoverrride
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
what do you mean ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220825 Visits: 46279
Your problem is that DataCompare is picking the wrong order for syncing the tables. Solution, rather than comparing and syncing the entire database, do it a table at a time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


crashoverrride
crashoverrride
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
the order issue is the small problem
the problem is that i have table "tbl_users" (code(index), firstname, email)
and table "tbl_priceoffers" (code(index), usercode(from tbl_users), price)

and it cannot insert "tbl_priceoffers" row without having @@identity from the recently inserted "tbl_users" row
basically the solution suppose to be something like :

to loop all missing users/priceoffers and :
insert into tbl_users (firstname email) select 'firstname','email'
set @code=select @@identity
insert into tbl_priceoffers (usercode, price) select @code, 100

it seems like i'm answering my question right here but i'm not sure that is the right solution..
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220825 Visits: 46279
Why not use identity insert and use the identity values from the other database? You did make sure they can't overlap when you brought the live server back up (via CheckIdent)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search