Restoring Differential Backup on an operational Database

Question

Restoring Differential Backup on an operational Database

Almas T.

SSC Rookie

Points: 29
More actions
January 20, 2007 at 12:01 am

#122735

Hi

We have a typical requirement.

We want to restore a Database on another server with Differential backup to make it an replica of server and we want to carry out some other transaction on the second server and same should not get changed when any new diff backup is restored on the second database.

But Periodically we would like to synchronize the 2 Servers probably using Diff Backup Method.

Purpose of second server is not a standby server but another server with same data for additional activities

Does anyone have any idea of doing it ?

Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

restore a full backup and then periodically resotre ur diff backup. but why can't u try replication for ur requirement if u r using sql server 2005 u can also try for db mirroring.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

dclark SSC Eights! Points: 843 More actions · Answer 2

You cannot restore a database, make changes to it, and then do additional restores that leave those changes in place.

You cannot do any changes to the database until you do a restore with recovery. Once you restore either a full or Differential backup with recovery, you cannot do any additional restores.

You can do a restore into standby mode and the data will be accessable, but you will not be able to do any changes to the database.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

yes clark u r right. i apologize. i dint read this phrase in the last post.

we want to carry out some other transaction on the second server and same should not get changed when any new diff backup is restored on the second database.

so if any ahanges are made the diff backup cant be used upon that.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Check out Red-Gate's SQL Data Compare. It's a wonderful tool for checking out and 'fixing' differences between two databases.

You tell it which two databases you want to compare and it will show you the differences for each one. Then it will create scripts for you to make whichever one you want match the other.

We use it quite frequently at my job.

-SQLBill

MohammedU SSCoach Points: 19644 More actions · Answer 5

You can use transactional replication and change the snapshot shot option "Keep the existing data unchanged"

MohammedU
Microsoft SQL Server MVP

Almas T. SSC Rookie Points: 29 More actions · Answer 6

Hi,

Thank U all for your suggestions, it helped me a lot in bringing me near to the possible solution.

I tried vaious options in replication. I didnt find the option "Keep existing data unchanged"; the option available in Snapshot is "Keep existing table unchanged". I want a restriction based only on table data, not on the schema.

And snapshot will be applied intially on the subscriber. but everytime data is posted, it won't skip updates. this must not happen as at the destination server data in few tables will be modified.

My MAJOR CONCERN IS Any Posting of data from Publisher(Source) to Subscriber(Destination) should not affect already posted data.

please let me know if u know any solution for this requirement.

Regards,

MohammedU SSCoach Points: 19644 More actions · Answer 7

I tried vaious options in replication. I didnt find the option "Keep existing data unchanged"; the option available in Snapshot is "Keep existing table unchanged". I want a restriction based only on table data, not on the schema.

I tried many times this option... may be you are looking at the wrong place...

Right Click publication/Properties/Articles/Click article properties (...) button/snapshot...

Then first radio button should be "Keep existing data unchanged"

MohammedU
Microsoft SQL Server MVP