SQL Server Performance

Adding a push subscription to a transactional publication

Discussion in 'SQL Server 2005 Replication' started by semje, Mar 16, 2009.

  1. semje Member

    Hi,
    I'm trying to create my subscriber by initializing it from a backup file. Within the publishing database I turned on 'Allow initialization from backup file to TRUE'. I backed up my publishing database. Then, I copied it to the subscribing server and restored it. Then, back on the publisher I'm running the following T-SQL:
    DECLARE
    @publication AS sysname;DECLARE
    @subscriber AS sysname;DECLARE
    @subscriptionDB AS sysname;DECLARE
    @backup AS sysname;SET
    @publication = 'Test1Publication';SET
    @subscriber = 'Named Instance';SET
    @subscriptionDB = 'Test1'SET
    @backup = '\servernamesharename est1_backup_200903161638.bak';USE
    Test1--Add a push subscription to a transactional publication.EXEC
    sp_addsubscription@publication
    = @publication,@subscriber
    = @subscriber,@destination_db
    = @subscriptionDB,@subscription_type
    = 'push',@sync_type
    ='initialize with backup',@backupdevicetype
    ='disk',@backupdevicename
    =@backup I keep getting the following:
    Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 252
    This database is not enabled for publication.
    Any help or ideas would be appreciated and thanks,
    Jeff
  2. techbabu303 New Member

    Dont understand the purpose of setting up push subcription on publisher using the backup file?
    Why not restore from the backup with overwrite option and use the script or Managment to studio to set up push subscriber ?
    Also have you installed SQL replication during installtion process ? which edition and SP of SQl server are used.
    -Sat
  3. semje Member

    Thanks...
    I feel very silly (to say the least). I was initializing the subscription from a backup. Problem was that I had the script stored in a project and when I opened it, it used the wrong connection string. I didn't notice the connect was wrong at the top of the query window. As soon as I had the right connection to the subscribing db server it worked like a charm.
    Thanks again for your response.

Share This Page