SQL Transactional Replication 2008 problems

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Transactional Replication 2008 problems

SQL Transactional Replication 2008 problems

Rate Topic

Display Mode

Topic Options

Author

Message

Modest DBA

Posted Wednesday, September 26, 2012 1:28 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 13, Visits: 237

Hi, i need help urgently on this one.
I have transactional replication in place between two servers. These db's are rather large so i initialized via a backup, which worked fine (i think). I now get a message on my repl monitor under (distributor to Subscriber history) it states: "The initial snapshot for publication "db" is not yet available".
Why would this be when i have initialized from backup? all transactions should be aligned?
Please help Wink

Post #1364480

Chris Golla

Posted Thursday, September 27, 2012 7:31 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 9:43 AM
Points: 75, Visits: 150

For some reason you need to initialize transactional replication using a sql script, there is no way to do it via SSMS. Also, when you created your publication, did u specify that it would be initialized by a backup? Try this link for the sql to initialize.

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

Post #1365252

Chris Golla

Posted Thursday, September 27, 2012 7:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 9:43 AM
Points: 75, Visits: 150

Here is a sample script

• Modify and execute the sql below on the publisher to create the subscription

exec sp_addsubscription @publication = N'<publication name', @subscriber = N'<instance>', @destination_db = N'<destination database>', @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = 'd:\rep.bak', @subscription_type = N'push', @update_mode = N'read only'
GO

Post #1365260

Modest DBA

Posted Thursday, September 27, 2012 7:44 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 13, Visits: 237

Hi thanks for the reply...
100%, the initialization was done via a sql script on SSMS. I also specified on publication to initialize from backup (if this is not enable the script won't run).
However the problem was that:
the script i had created a push subscription which i subsequently deleted, and created a pull subscriptions(simply to reduce load on the primary server).
when i created the new subscription i did not specify "not to initialize from a snapshot".
This caused the subscription not the talk correctly to the distributor.
Thanks for the help guys!

Post #1365270

« Prev Topic | Next Topic »

Permissions