asked on Mass update any changed rows in db from live to backup
All,
I need to update my back up each night with any newly inserted rows or existing row updates from teh live environment to my backup.
Do i have to write an individually script for every table for each newly inserted or updated record or is it possible to do this another way. I have to avoid deleting any records so dont think i can use log shipping
I need to update my back up each night with any newly inserted rows or existing row updates from teh live environment to my backup.
Do i have to write an individually script for every table for each newly inserted or updated record or is it possible to do this another way. I have to avoid deleting any records so dont think i can use log shipping
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
slam69
ASKER
triggers wont work as we are looking at 60+ tables with a pretty high transaction rate so dont want to impact IO by having a live trigger fire off to teh other db.
Needs to be nightly process, think Im going down the ssis route but I havent used that for quite a while!!
Needs to be nightly process, think Im going down the ssis route but I havent used that for quite a while!!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I like it but it will include delete statements which I need to ignore and I dont think I can tell log shipping to only process update or insert statements?
yes, that is true. your requirement is very specific, you have to develop architecture by your own. I don't think so there is something really very ready to use for you.
ASKER
looking at red gate data compare now
you can use SQL server ETL tool and develop SSIS which have very good performance
this can be helpful
http://msdn.microsoft.com/en-us/library/ms141026.aspx
this can be helpful
http://msdn.microsoft.com/en-us/library/ms141026.aspx
ASKER
@sysdba - ,SSIS is ok but the only two ways of doing it, SDC and merge join both take quite a while to complete and this is going to be processing through millions of records
one expert to another---- try and take a thorough look through what else has been posted in a question, your link is pretty generic and doesnt really give a good guide to this task, but thanks for your input. If you can give any other suggestions all input welcomely recvd
Cheers jay
one expert to another---- try and take a thorough look through what else has been posted in a question, your link is pretty generic and doesnt really give a good guide to this task, but thanks for your input. If you can give any other suggestions all input welcomely recvd
Cheers jay
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
hi guys sorry for delay in update but i had some issues to deal with
I have found one reasonble solution and that is red gate softwares data compare. This allows you to compare two dbs of same structure and identify rows which are different in both source, target and ones which dont exist in either or. Youc an then choose which rows you want to synchronize and how.
really good piece of kit and pretty cheap at under £300 as well. Can be run from command line so can batch file operate and on our 80GB db it takes a little over an hour to run, my ssis package was at 3 hours just for two big tables!!
I have found one reasonble solution and that is red gate softwares data compare. This allows you to compare two dbs of same structure and identify rows which are different in both source, target and ones which dont exist in either or. Youc an then choose which rows you want to synchronize and how.
really good piece of kit and pretty cheap at under £300 as well. Can be run from command line so can batch file operate and on our 80GB db it takes a little over an hour to run, my ssis package was at 3 hours just for two big tables!!
If you prefer to still do it nightly and your data tables include audit fields, you can right a stored procedure to retrieve all records changed from the prior day and process the records that way. You can use the SQL Job Agent to schedule this process.