Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Transactional Replication 2008 problems Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 1:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 06, 2013 12:45 AM
Points: 51, Visits: 321
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
Post #1364480
Posted Thursday, September 27, 2012 7:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, September 27, 2012 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, September 27, 2012 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 06, 2013 12:45 AM
Points: 51, Visits: 321
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 »

Add to briefcase

Permissions Expand / Collapse