Uninitialized subscription in Transactional replication

  • Dj463

    Ten Centuries

    Points: 1181

    Hello all,

    I setup replication from the backup of publisher database by following these steps:

    1. Set up the Distributer – database and share – one time setup

    2. Set up Publisher on source server - Don’t use either checkbox for snapshot.

    3. Enable the flag under the Publication properties to allow “initialize from backup”. (Right-click on the publication, properties)

    4. Disable the distribution cleanup agents. (Under SQL agent jobs)

    5. Make a Full backup of the database. Keep a local copy as you'll need it later.

    6. Copy database to other site. (Over the network, courier pigeon, magic, whatever)

    7. Restore database with the same name

    8. Create pull subscription on the destination server. (scripts below)

    Script 1:

    -----BEGIN: Script to be run at Subscriber 'Subscribing_SQLServerName'-----------------

    use [Your_DB_Name]

    exec sp_addpullsubscription @publisher = N'Publishing_SQLServerName', @publication = N'Your_DB_Name_PUB', @publisher_db = N'Your_DB_Name', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0

    Script 2:

    exec sp_addpullsubscription_agent @publisher = N'Publishing_SQLServerName', @publisher_db = N'Your_DB_Name', @publication = N'Your_DB_Name_PUB', @distributor = N'Publishing_SQLServerName', @distributor_security_mode = 0, @distributor_login = N'dist_login_acct', @distributor_password = N'dist_password', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090902, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'domain\username', @job_password =N'user_password', @publication_type = 0

    GO

    -----END: Script to be run at Subscriber 'Subscribing_SQLServerName'-----------------

    Script 3:

    -----BEGIN: Script to be run at Publisher 'Publishing_SQLServerName'------------

    ------- backupdevicename has to be located on the Publisher machine -------

    use [Your_DB_Name]

    exec sp_addsubscription @publication = N'Your_DB_Name_PUB', @subscriber = N'Subscribing_SQLServerName', @destination_db = N'Your_DB_Name', @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = 'e:\BACKUP\Your_DB_Name090209.bak', @subscription_type = N'pull', @update_mode = N'read only'

    GO

    -------END: Script to be run at Publisher 'Publishing_SQLServerName'-------------

    Now the subscriber is created, but when i check the replication monitor, it gives me uninitialized subscription. When i go check the distribution agent at the subscription it says "cannot connect to distributor". It is the same error when i check the "view syncronization status".

  • Dj463

    Ten Centuries

    Points: 1181

    I am actually using pull subscription

  • dastagiri16

    Hall of Fame

    Points: 3370

    is it resolved ..i followed same steps and getting same error...please let us know if it works or not

    cant we configure transactional replication from full backup ...its working while taking tlog backup and setup..

    The below link is having steps if you have option to gp with tlog backups

    http://www.sqlpassion.at/archive/2012/08/05/initialize-a-transactional-replication-from-a-database-backup/

Viewing 3 posts - 1 through 3 (of 3 total)

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