start a transactional replication with a backup file

Question

start a transactional replication with a backup file

desassa

SSC Veteran

Points: 225
More actions
December 2, 2009 at 8:22 am

#213144

Hello forum,
I know it is possible to start a transaction replication with a db restore on the subscriber side so without the snapshot agent stuff. I want to do this because our db is 210 gb big (or small).
Of course between taking the init backup, copying db, restoring the primary db on the subscriber the primary db on publisher side has updated (no option to put things on hold unfortunately)
How can I prevent losing data between these backup-copy-restore moments ?
I can take a transaction log backup but still I have the same problem because it takes some time
to backup, copy and restore the log backup.
Can somebody point me in the right direction.
As always thanks a lot ...
Steven

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Have a read of articles "How to: Initialize a Transactional Subscriber from a Backup" and "Initializing a Transactional Subscription Without a Snapshot " in Books Online. I think everything you need is in these two articles.

cjeremy74 Ten Centuries Points: 1135 More actions · Answer 2

This maybe addressed in the recommended BOL articles, but if you are trying to get the 2 db's synced as closely as possible - I would recommend restoring the full backup, then applying logs backups (which should be much smaller and faster to backup, copy and apply).

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

cjeremy74's solution sounds more like Log Shipping, not replication.

When creating a subscription in Transaction Replication when you wish to initialise using a backup, you specify the name of the database backup file/device. Replication uses this to work out what transaction it needs to start from when sending changes to the subscriber. No need to worry about missing transactions (unless the backup is too old and the transactions are not available in the transaction log of the publisher)

desassa SSC Veteran Points: 225 More actions · Answer 4

happycat59 / cjeremy74 thnx for replies and input.

TSQL did do the trick through the sp_addsubscription (and not the wizard obviously)

Steven