How do we refresh the new updated fresh database with the old database without replacing the database? Old Database would be the client database and we cannot the directly replace the database on the client database as it will consist live data. Our requirement is to refresh new changes on on the old database without hampering the client live data. Its likely to be an project upgrade.Can someone assist me on this,how can we achieve this without using any third party tool. I am aware how to achieve this by making manual script. But is there any other way that we can implement this more smoothly,as it will be happening very often and running manual script will be time consuming.
My suggestion would be to get the client to make a backup of their database. You restore that to a machine that you can access. Then, use the Visual Studio 2012 database compare feature to compare between the client database and your new database to generate a script. You test the script to validate that it will do what you want. Once you're sure it will succeed without causing dropped data, send it the client to have them run it.
We have a similar process : 1) What we are applying is the use of transactional replication which replicates data from old to new as from publisher to subscriber. 2) Easiest way would be to implement log shipping. Depending upon the time of refresh you can set the jobs both on primary and secondary servers where your new and old db resides. Simple copying/shipping of logs from source to destination DB and restoring per LS exe scripts. Above two are much better than any manual scripting. All you need to analyse is you're requirement along with amount of resources available with you in terms on disk space, memory and CPU available. Refer to MS link for setting up Replication and Log shipping per you're needs! Thanks, hope this helps.