restore overwritten replication data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

restore overwritten replication data?

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2010-11-04 : 14:38:37

If I have one 2008 server replicating to another using transactional replication, and I accidentally change data in the subscriber database, what's the best way to get it to match the publisher again? Would I need to create a new snapshot on the publisher?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-04 : 14:49:42

Couldn't you just run a T-SQL statement to sync with the publisher, using a linked server if needed?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-04 : 14:50:28

Do you know what tables and columns were accidentally changed on the subscriber?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2010-11-05 : 11:11:04

I'm just thinking hypothetically, but I assume that I would know which tables and columns were involved if it happened. I could set up a linked server if I needed to... are you thinking I would wipe out all the data in the affected table(s) and repopulate them with data from the publisher? I suppose if I had Red Gate's SQL DataCompare I could just use that to sync the data.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-05 : 11:17:52

If it's via linked server, you'd just do something like this:

UPDATE	a
SET	col1 = b.col1,
	col2 = b.col2,
	col3 = b.col3
	etc
FROM	TableA a
JOIN	otherServer.Database.dbo.TableA b
On	a.primaryKey = b.primaryKey

If it is a small publication, easiest and cleanest to just run the snapshot agent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources