Transactional Replication using initialize with backup

Question

Transactional Replication using initialize with backup

sqlrumble

Default port

Points: 1458
More actions
March 26, 2010 at 6:01 am

#217075

Hello,
We currently have a replicated database from one country to another on (SQL 2005 > SQL Server 2000). The database is a push transactional replication model from country A (We are in country B).
The database is over 100GB in size and so creating a snapshot and pushing this over the wire is a big resource and time consuming task.
My thinking was as we already have a copy of the database over in country B (replicated), taking a backup of the database, restoring it onto our new environment (SQL Server 2008) and getting the guys in country A to start a new push subscription to this database. Is that possible?
I sumise the steps would be something like:
1) Setup subscription as intialize from backup in Country A
2) Pause replication to current database
3) Take full backup of replicated database and restore to new server with norecovery
4) When ready, take final tlog backup, restore onto database with recovery
5) Start Push subscription from Country A.
I presume there will be some manual steps then to set the subscribed database to be setup as a subscriber.
I have read that this is possible when using a backup of the actual publication database, but wondered if this was possible from a backed up subscribed database.
The replication will be from a SQL 2005 instance to a SQL 2008 instance.
Would the above work? Any suggestions would be greatly apprieciated.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

mike mcneer Ten Centuries Points: 1189 More actions · Answer 1

yes and no. You will have to drop replication from the subscriber database otherwise it will have the settings thinking it is being replicated to. There are some stored procs to drop any reference to replication. If you cant find it easily let me know, I can dig it up.

Otherwise your list looks pretty close as long as the schemas match.

Although I am not sure about step 1 why would you setup the subscription as initialize? Will this not attempt to copy over the db and data from Server A which is what you are trying to avoid?

I would pause replication to subscriber B

backup subscriber B and move to new server.

Drop any replication reference from newly restored db.

apply any missed tran logs from subscriber to new system

Setup subscription without initliazing from A -B and let it roll.

I think that is basically what you are getting at?

Thanks,

Mike McNeer

sqlrumble Default port Points: 1458 More actions · Answer 2

Many thanks for your response mike.

I'll give it ago as per your suggested steps. If it easy for you to find what needs removing, I'd really apprieciate it (give me somewhere to start), if not its ok, I'll try and work it out.

I'll give this ago and let you know how I get on.

I was using information from the following links:

http://msdn.microsoft.com/en-us/library/ms147834.aspx

http://kodeexii.net/current-events-computer-technology/transactional-replication-without-snapshot.html

The below I've just read, but dont feel all the steps are necessary:

http://weblogs.sqlteam.com/dinakar/archive/2007/04/02/60158.aspx

Thanks again.

mike mcneer Ten Centuries Points: 1189 More actions · Answer 3

The command I was looking for earlier is:

sp_removedbreplication

You would not need this if you were dropping the subscription first. So in order to be able to setup a subscription on it you need the above.

This should remove any reference of replication from the moved db so after running this there will be no remnants of anything to do with replication on that DB. Let us know how it goes.

Thanks,

Mike McNeer

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

Hello,

Before dropping anything, it is better to script your replication settings first. I would go for creating both : Drop and Create scripts.

This will be more useful if you want to drop the replication and then recreate it. You can initialize it from backup also by making some changes to the script that you have handy.

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂