1

I have a database which i want to replicate to another server. Since the database is huge(~1.5 TB) i have decided to do initialize from backup. But since it is a production live database,new insert/updates happen all the time. I have done intialiaze from backup from a full backup earlier. I want to know if i can implement transactional replication from a full backup + differential backup.

So when i create subscriber using the below script,do i have to give the differential.bak as the backupdevicename or the fullbackup file name.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup', 
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\differential.bak'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------



-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012', 
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', 
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012', 
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @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 = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', 
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

Since it is a live database,i cannot make the database into single user mode and take full backup and initialize the subscriber.I have trans logs also being taken every hour.So i am planning to restore recent full backup + differential to subscriber and will disable trans log backup job temporarily. Is this a correct approach.? if anyone encountered similar situation ,please let me know.

  • Perhaps, you should consider "initialize from lsn" instead of "initialize with backup" so that you will be able to restore your subscriber database to maximum lsn that possible. – Denis Rubashkin Aug 1 '18 at 7:13
  • yeah.I didn't know about 'initialize from lsn'.Can i also achieve this with 'initialize with backup' as i mentioned in the post.i,e Re cent full backup + differential. – user9516827 Aug 1 '18 at 13:18
  • 1
    If to believe this article you can restore your DB with norecovery and then intialiaze using @backupdevicename= 'your transaction log last backup' sqlservercentral.com/blogs/aschenbrenner/2012/08/05/… – Denis Rubashkin Aug 1 '18 at 13:56
2

You don't have to take the publisher offline when you initialize from a backup. Just like initializing from a snapshot, the initialization just needs to bring the subscriber database to a point-in-time within the Distribution Retention Period, and to configure the subscription with the LSN to start from.

Perhaps you're thinking of Manual Initialization which requires you to synchronize the subscriber before beginning repilication.

|improve this answer|||||
  • okay.I am trying to use initialize from backup with the latest differential on the live database..Distribution retention period is default and i haven't changed.The full backup was taken previous night 10 PM and differential was taken today morning 10 AM.And while adding subscription i will mention the differential for backupdeviceThat's the plan – user9516827 Aug 1 '18 at 14:40
  • Let me know if that works. I think the backup used when adding the subscription is just used to determine the LSN to which the subscriber was restored, but haven't tested. – David Browne - Microsoft Aug 1 '18 at 14:55
  • Full Backup + Differential didn't work.But i did Full backup + all trans log backup and then configured subscription with backupdevice as last trans log backup.It worked like a charm.I was very skeptical about it,because i took differential after last trans log backup. @David Browne – user9516827 Aug 1 '18 at 17:26
2

Let this be helpful to someone who will have to deal with huge database and have to implement replication without initialization. I had set up transactional replication without initialization using backup earlier ,before my database was live.So i was not worried.Recently when i tried to add few articles together it caused a deadlock and my subscriber became inactive with the below message.

The subscription to this publication is not active yet. No user action is required

I researched about this and everybody was pointing me towards dropping the subscription and create a new one,because most of them use snapshot to initialize.But since i use initialize with a backup,and it is production environment(with it's share of performance issues) i was worried. But anyways i had no other way but to drop the subscriber and create a new one and internalize with backup.

For me most recent full backup was previous night's full backup. I had publication already setup with initialize from backup set to true. Then i restored the full backup followed by all transaction log backup. After restoring the database in subscriber,i added subscriber using the below script.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup', 
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\last_trans_log.trn'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------



-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012', 
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', 
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012', 
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @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 = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', 
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

You can see that @backupdevicename=N'C:\Rep\Replication_Scripts\last_trans_log.trn'. it worked.

I was hoping to use a differential backup after recent full backup,but for some weird reasons,i got message that the full back up was not set correctly for the differential.I even checked LSN of the full backup from the differential logs. If someone succeeds with differential it will be great.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.