Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help with best practice for merging 2 sql databases Expand / Collapse
Author
Message
Posted Saturday, May 18, 2013 5:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:55 AM
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!
Post #1454246
Posted Saturday, May 18, 2013 7:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 43,045, Visits: 36,204
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 2008, MVP
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

Post #1454258
Posted Saturday, May 18, 2013 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:55 AM
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 ?
Post #1454271
Posted Saturday, May 18, 2013 9:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 43,045, Visits: 36,204
Sync table by table?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1454273
Posted Saturday, May 18, 2013 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:55 AM
Points: 4, Visits: 12
what do you mean ?
Post #1454278
Posted Saturday, May 18, 2013 11:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 43,045, Visits: 36,204
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 2008, MVP
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

Post #1454281
Posted Saturday, May 18, 2013 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 5:55 AM
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..

Post #1454283
Posted Saturday, May 18, 2013 12:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 43,045, Visits: 36,204
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 2008, MVP
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

Post #1454288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse