Create a Subscription

After the reporting (Subscriber) database is restored, you must create a subscription to the Publication by completing the following steps:

1. On the Publisher SQL Server, execute the following script in the context of the Publication database to create the subscription. Change the values of the following parameters appropriately:

   1. @backupdevicename – Location of the folder where the full backup file is stored

   2. @publication – Name of the Publication specified while creating the Publication

   3. @subscriber – Name of the SQL Server instance where the subscription database is located

   4. @destination_db – Name of the subscription database

   5. @subscriber_db – Name of the subscription database

   
   -- MAKE SURE TO EXECUTE THIS ON THE PUBLISHER DATABASE
   EXEC sp_addsubscription 
   	@sync_type = 'initialize with backup'
   	, @backupdevicetype = 'disk'
   	, @backupdevicename = 'Location of Backup File with backup file name and extension' --<< Change here
   	, @publication = 'Name Of Publication' --<< Change here
   	, @subscriber = 'Subscriber Server name' --<< Change here
   	, @destination_db = 'Subscriber Database name' --<< Change here
   	, @subscription_type = 'push'
   Go
   EXEC sp_addpushsubscription_agent 
                      @publication = 'Name Of Publication'  --<< Change here
                    , @subscriber = 'Subscriber Server name'  --<< Change here
                    , @subscriber_db = 'Subscriber Database name'  --<< Change here
                    , @subscriber_security_mode = 1
   Go
   
   /* Example: 
   	EXEC sp_addsubscription 
   		@sync_type = 'initialize with backup'
   		, @backupdevicetype = 'disk'
   		, @backupdevicename = 'D:\SQLDATA\BACKUP\CVUE_DEMO.bak'
   		, @publication = 'CVUE_DEMO_PUB'
   		, @subscriber = 'CVUESQLSUB\INST1'
   		, @destination_db = 'CVUE_DEMO_SUB'
   		, @subscription_type = 'push'
   
   	EXEC sp_addpushsubscription_agent 
   @publication = 'CVUE_DEMO_PUB'
   , @subscriber = 'CVUESQLSUB\INST1'
   , @subscriber_db = 'CVUE_DEMO_SUB' 
   , @subscriber_security_mode = 1
   */
   

   You may see an exception in the replication monitor before step 3 is executed. Please ignore the exception.

2. On the Subscription Options page of the Publication Properties dialog box, set the Allow initialization from backup files property to False.  

   

3. After the subscription is created and the above options are set, execute the following cleanup replication script against the reporting (Subscriber) database that was just restored:

   
   Exec [dbo].[cmc_DropConstraintsAndTriggersOnSubscriber]
   GO
   IF EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.SyIndexRecreatePostUpgradeWork'))
   AND EXISTS (SELECT 1 FROM sys.columns WHERE is_identity = 1
   AND COLUMNPROPERTY(OBJECT_ID('dbo.SyIndexRecreatePostUpgradeWork'), 'SyIndexRecreatePostUpgradeWorkID', 'IsIdNotForRepl') = 1
   )
   BEGIN
   	ALTER TABLE dbo.SyIndexRecreatePostUpgradeWork ALTER COLUMN SyIndexRecreatePostUpgradeWorkID DROP NOT FOR REPLICATION
   END
   GO
   

4. Run the following queries against the Subscriber database to ensure 0 rows are returned. This action will enable you to confirm that all the objects that were supposed to be dropped (in the preceding step) are dropped.

   
   SELECT OBJECT_NAME(parent_object_id) as ObjectName, def.name as DefaultConstraintName
   FROM SYS.DEFAULT_CONSTRAINTS def WITH (NOLOCK)
   INNER JOIN (SELECT DISTINCT Article FROM [dbo].[MSreplication_objects] WITH (NOLOCK)) article ON OBJECT_NAME(def.PARENT_OBJECT_ID) = Article.article
   WHERE OBJECTPROPERTY(def.PARENT_OBJECT_ID, 'IsTable') = 1
   GO
   
   SELECT  TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
   INNER JOIN (SELECT DISTINCT Article FROM [dbo].[MSreplication_objects] WITH (NOLOCK)) article ON TABLE_NAME = Article.article
   WHERE CONSTRAINT_TYPE <> 'PRIMARY KEY'
   GO
   
   SELECT OBJECT_NAME(parent_id) as TableName, name as TriggerName
   FROM sys.triggers trg WITH (NOLOCK)
   INNER JOIN (SELECT DISTINCT Article FROM [dbo].[MSreplication_objects] WITH (NOLOCK)) article ON OBJECT_NAME(trg.parent_id) = Article.article
   WHERE trg.parent_class = 1 AND OBJECT_NAME(trg.parent_id) NOT IN ('SyReplTablesAltered')
   
   GO
   

5. Re-enable the replication jobs that were disabled in Step 10 of the Modify the Publication Properties section. To re-enable them, right-click the name of the SQL Server job and then click Enable:

   • Distribution cleanup job (Distribution clean up: distribution).

   • Replication Snapshot Agent job with the following name: <Publisher Server Name>-<Publication Database Name>-<Publication Name>-#.

     The Job Category would be ‘REPL-Snapshot’.

     Example:  In the image below, the Replication Snapshot Agent job is named CVUESQL-CVUE_DEMO -CVUE_DEMO_PUB-10.