Initialize Subscriber from Differential Backup

My colleague Lubín Hidalgo Carro, Microsoft SQL Support Engineer, was helping a customer initialize the Transactional Replication subscriber via backup\restore method as documented here and on docs.microsoft.

https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/

https://docs.microsoft.com/en-us/sql/relational-databases/replication/initialize-a-transactional-subscription-from-a-backup

The main steps shown below are to create the publication, change allow init from backup, then take a full backup.

1. Create the publication using user interface Replication Wizard.
   
2. Under subscription options for the publication, set “allow initialization from backup files” to true
   
3. Create a new full backup of the publisher database. If you have existing full backup of the publisher database, you can still use that backup set but we have take a new log backup or differential backup of the publisher database and restore at the subscriber.
   
4. Replication UI does not allow the option to create the subscription to allow initialization from back. We have to TSQL when creating the subscription.
   exec sp_addsubscription @publication = N’Repl2000′, …..
   @sync_type = N’initialize with backup’,

   @backupdevicetype=‘Disk’,
   @backupdevicename=‘C:\Repl2000_RestoreThis.bak’–this is the last backup used to restore on the subscriber that was taken after the publication was created
   go
   
   exec sp_addpushsubscription_agent …….
   go
   

His customer called when replication failed with error shown below.

The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup. The subscription to publication ‘Transactional’ has expired or does not exist.

Turns out our customer took the backup before the publication was created, step 0.5 if you will. User transactions created between when backup was taken, and Publication created (Log Reader running) would not be cached in the Distribution database resulting in missing metadata. What to do?

Catch up Subscriber

Lubin’s solution was to drop the subscription, “catch up” the subscriber using a differential backup, then add back the subscriber referencing the differential backup in @backupdevicename parameter for sp_addsubscription.  Another way to “catch up” the subscriber would be via transaction log restores.

To learn more, you can walk through his example using steps below

Walk Through – Initialize Subscriber via Differential Backup\Restore

The walkthrough below is based on published database called ReplDB with one table called ReplTable.

--Publisher 
BACKUP DATABASE [ReplDB] TO  DISK = N'C:\tmp\repl_backups\ReplDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReplDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Subscriber
USE [master]

RESTORE DATABASE [ReplDB] FROM  DISK = N'C:\tmp\repl_backups\ReplDB.bak' WITH NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

-- Publisher
insert into ReplTable (number) values (1);

select * from ReplTable; 

number
--------------------------------------------------
1

-- Publisher
use [ReplDB]
 exec sp_replicationdboption @dbname = N'ReplDB', @optname = N'publish', @value = N'true'

GO
exec [ReplDB].sys.sp_addlogreader_agent @job_login = N'login_name', @job_password = null, @publisher_security_mode = 1, @job_name = null
GO
-- Adding the transactional publication. Change the login and password as needed

exec sp_addpublication @publication = N'PublicationTest', @description = N'Transactional publication of database ''ReplDB'' from Publisher ''SQL1\SQL2016''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'true', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'PublicationTest', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'login_name', @job_password = null, @publisher_security_mode = 1
GO

exec sp_addarticle @publication = N'PublicationTest', @article = N'ReplTable', @source_owner = N'dbo', @source_object = N'ReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'ReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboReplTable', @del_cmd = N'CALL sp_MSdel_dboReplTable', @upd_cmd = N'SCALL sp_MSupd_dboReplTable'
GO

-- Publisher
insert into ReplTable (number) values (2);

select * from ReplTable;
number
--------------------------------------------------
1
2

--Publisher
BACKUP DATABASE [ReplDB] TO  DISK = N'C:\tmp\repl_backups\ReplDB_diff.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'ReplDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--Subscriber
RESTORE DATABASE [ReplDB] FROM  DISK = N'C:\tmp\repl_backups\ReplDB_diff.bak' WITH RECOVERY
GO

select * from ReplTable;

number
--------------------------------------------------
1
2

--Publihser
USE [ReplDB]
GO
-- Add the Subscription on the Publisher (note DIIF.bak)
EXEC sp_addsubscription
@publication = N'PublicationTest', 
@subscriber = N'SQL2\SQL2016', 
@destination_db = N'ReplDB', 
@sync_type = N'initialize with backup', 
@backupdevicetype='Disk', 
@backupdevicename='C:\tmp\repl_backups\ReplDB_diff.bak',
@subscription_type = N'pull', 
@update_mode = N'read only'
GO

7. Transaction #3 on source (to be publisher)

--Publisher
insert into ReplTable (number) values (3);
GO

select * from ReplTable;
GO
 number
--------------------------------------------------
1
2
3

--Subscriber
use [ReplDB]
-- Add the Pull Subscription
EXEC sp_addpullsubscription
@publisher = N'SQL1\SQL2016', 
@publication = N'PublicationTest', 
@publisher_db = N'ReplDB', 
@independent_agent = N'True', 
@subscription_type = N'pull', 
@description = N'', 
@update_mode = N'read only', 
@immediate_sync = 0
GO
-- Add the Pull Subscription Agent. Change login and password
EXEC sp_addpullsubscription_agent
@publisher = N'SQL1\SQL2016', 
@publisher_db =  N'ReplDB',  
@publication = N'PublicationTest',
@distributor = N'SQL1\SQL2016', 
@distributor_security_mode = 1, 
@distributor_login = N'', 
@distributor_password = null, 
@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 = 20200801, 
@active_end_date = 99991231, 
@alt_snapshot_folder = N'', 
@working_directory = N'',
@use_ftp = N'False', 
@job_login = '', 
@job_password = '', 
@publication_type = 0
GO

--Subscriber
select * from ReplTable;
GO
 number
--------------------------------------------------
1
2
3

Written by
Lubin Hidalgo Carro
Microsoft SQL Server Support Services
Posted by
Chris Skorlinski
Microsoft SQL Server Escalation Services