May 18, 2013 at 5:20 am
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!
May 18, 2013 at 7:36 am
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
May 18, 2013 at 9:22 am
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 ?
May 18, 2013 at 9:30 am
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
May 18, 2013 at 10:23 am
what do you mean ?
May 18, 2013 at 11:08 am
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
May 18, 2013 at 11:40 am
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..
May 18, 2013 at 12:40 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply