Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automating SQL Server Transactional Replication Initialization from a Backup


By:   |   Updated: 2014-10-29   |   Comments (3)   |   Related: More > Replication

Problem

There are situations where you might have a large SQL Server database that you need to replicate. Since the database is very large we want to initialize the Subscriber from the SQL Server backup and not use a snapshot.  How do we bundle all the steps together to achieve this without performing them manually?

Solution

I want to cover the basic steps needed to setup a "Push Type Transactional Replication".  This tip assumes you already have an understanding of SQL Server Transactional Replication and I will show how to bundle all the steps into one process. 

Before we go get started, let's cover some acronyms/abbreviations used throughout the discussion.

  • PubServer - Publisher Server
  • DtbServer - Distribution Server
  • SubServer - Subscriber Server

Assumptions/Notes: Publisher, Distributor and Subscriber are identified and configured correctly.

First create a batch file (SetupReplSteps.bat) like below.  This will be the calling process that will call each individual script for each step of the process.  If you review this you can see that I have 8 steps that will be run and they are discussed in more detail below.

echo ' ' > C:\ReplSteps\RepLog.txt
echo *********************** Step 1 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\1_CreatePublication.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 2 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"DtbServer" -E -i"C:\ReplSteps\2_DisableDistCleanAgentJob.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 3 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\3_BackupPublisherDatabase.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 4 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\4_CopyBakFileFromPubToSub.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 5 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\5_RestorePubDatabaseToSub.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 6 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"SubServer" -E -i"C:\ReplSteps\6_DisableAllDbTriggersOnSub.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 7 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"PubServer" -E -i"C:\ReplSteps\7_AddSubscriptionOnPub.sql" >> C:\ReplSteps\RepLog.txt

echo ' ' >> C:\ReplSteps\RepLog.txt
echo *********************** Step 8 *********************** >> C:\ReplSteps\RepLog.txt
SQLCMD -S"DtbServer" -E -i"C:\ReplSteps\8_StartDistribCleanupAgent.sql" >> C:\ReplSteps\RepLog.txt

It is a good practice to log the execution result of all the steps for troubleshooting purposes. Hence, I have re-directed the output to a text file "RepLog.txt".  Adjust the above code as needed for your environment.

Once the .bat file is laid out, the steps become self explanatory.

Step 1: Script your publication

The easiest way to do this is to use SSMS to create the publication and then script out the code or if you prefer you can write the code yourself.  In the below example I create a publication and added one article to replicate.

USE [DBname]
GO
EXEC sp_replicationdboption @dbname = N'DBname'
,@optname = N'publish'
,@value = N'true'
GO
-- Adding the transactional publication
USE [DBname]
GO EXEC sp_addpublication @publication = N'DBname_Repl'
,@description = N'Transactional publication of database ''DBname'' from Publisher ''PHLSQL''.'
,@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'DBname_Repl'
,@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 = NULL
,@job_password = NULL
,@publisher_security_mode = 1 GO
USE [DBname]
GO
EXEC sp_addarticle @publication = N'DBname_Repl'
,@article = N'ACL'
,@source_owner = N'dbo'
,@source_object = N'ACL'
,@type = N'logbased'
,@description = NULL
,@creation_script = NULL
,@pre_creation_cmd = N'drop'
,@schema_option = 0x000000000803509F
,@identityrangemanagementoption = N'manual'
,@destination_table = N'ACL'
,@destination_owner = N'dbo'
,@vertical_partition = N'false'
,@ins_cmd = N'CALL sp_MSins_dboACL'
,@del_cmd = N'CALL sp_MSdel_dboACL'
,@upd_cmd = N'SCALL sp_MSupd_dboACL' GO

Note I have set @allow_initialize_from_backup = N'true'. This will let us use the backup to initialize replication.

The above code for your publication should be saved in a file called "1_CreatePublication.sql".

Step 2: Disable the replication clean up job

The next step will disable the replication cleanup job.

USE distribution
GO EXEC msdb..sp_update_job @job_name = 'Distribution clean up: distribution', @enabled = 0 GO EXEC msdb..sp_stop_job @job_name = 'Distribution clean up: distribution' GO

Note: We have to do this in our Production environment to ensure if there are issues in the subsequent steps, the transactions are not deleted from the Distributor. This relates to the Minimum Retention time parameter configured on the Distributor.

The above code should be saved in a file called "2_DisableDistCleanAgentJob.sql".

Step 3: Backup the Publisher Database

The next step is to create a backup of the published database.

USE master
GO

BACKUP DATABASE DBname TO DISK = 'T:\DBname.bak'
GO

The above code for your database should be saved in a file called "3_BackupPublisherDatabase.sql".

Step 4: Move the backup file to the Subscriber

We now need to copy the backup file from the publisher to the subscriber server.  I am using xp_cmdshell and XCOPY to copy the backup.  I am also turning on xp_cmdshell using sp_configure and then turning it off at the end.

sp_configure 'xp_cmdshell', 1
GO
reconfigure
GO

EXEC master..xp_cmdshell 'XCOPY "\\PubServer\T$\DBname.bak" "\\SubServer\G$" /J /Y'
GO

sp_configure 'xp_cmdshell', 0
GO
reconfigure
GO

The above code for your database backup should be saved in a file called "4_CopyBakFileFromPubToSub.sql".

Step 5: Restore the database on the Subscriber Server

Now we need to restore the database backup on the subscriber.  If the database already exists on the subscriber I am taking it offline and then doing the restore.

USE master
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DBname')
   ALTER DATABASE DBname SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

USE master
GO

RESTORE DATABASE DBname
FROM DISK = '\\SubServer\G$\DBname.bak'
WITH MOVE 'DBname' TO 'R:\MSSQL\DBname.mdf'
,MOVE 'DBname_log' TO 'S:\MSSQL\DBname_Log.ldf'
,STATS = 5
,REPLACE;
GO

The above code for your database restore should be saved in a file called "5_RestorePubDatabaseToSub.sql".

Step 6: Disable any triggers on the Subscriber

This step is only needed when Triggers are present.  We don't want them to fire on the subscriber, so we disable all triggers in the database.  I have listed the triggers for my table, but you could create T-SQL code to find and disable all triggers in the database.

USE DBname<
GO
DISABLE TRIGGER trg_ReportWorkflowSave ON ReportPreference; DISABLE TRIGGER trg_ReportWorkflowReportTbl ON Report;

The above code for your database triggers should be saved in a file called "6_DisableAllDbTriggersOnSub.sql".

Step 7: Add the Subscription

The following code will add the subscription.  Note that we are using push replication.

USE DBname
GO
EXEC sp_addsubscription @publication = N'DBname_Repl'
,@subscriber = N'SubServer'
,@destination_db = N'DBname'
,@subscription_type = N'Push'
,@sync_type = N'initialize with backup'
,@backupDeviceType = N'Disk'
,@backupDeviceName = N'T:\DBname.bak'
,@update_mode = N'read only'
,@subscriber_type = 0
GO
EXEC sp_addpushsubscription_agent @publication = N'DBname_Repl'
,@subscriber = N'SubServer'
,@subscriber_db = N'DBname'
,@job_login = NULL
,@job_password = NULL
,@subscriber_security_mode = 1
,@frequency_type = 64
,@frequency_interval = 1
,@frequency_relative_interval = 1
,@frequency_recurrence_factor = 0
,@frequency_subday = 4
,@frequency_subday_interval = 5
,@active_start_time_of_day = 0
,@active_end_time_of_day = 235959
,@active_start_date = 0
,@active_end_date = 0
,@dts_package_location = N'Distributor' GO

The above code for your subscriber should be saved in a file called "7_AddSubscriptionOnPub.sql".

Step 8: Enable the Distribution clean up job

USE distribution
GO

EXEC msdb..sp_update_job @job_name = 'Distribution clean up: distribution', @enabled = 1;
GO

EXEC msdb..sp_start_job @job_name = 'Distribution clean up: distribution'
GO

The above code should be saved in a file called "8_StartDistribCleanupAgent.sql".

Next Steps
  • Test the script in a Development or Test environment and validate the results.
  • Setup the batch file as a SQL Agent Job in order to further automate the process.


Last Updated: 2014-10-29


get scripts

next tip button



About the author
MSSQLTips author Prashant Shekhar Prashant Shekhar is employed as a DBA with BioTelemetry; specializing in HA solutions comprised of SQL Clustering, Replication and Log Shipping.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 22, 2016 - 2:01:02 PM - Tom Goff Back To Top

The MSDN documentation (https://msdn.microsoft.com/en-us/library/ms147834.aspx) has the following warning:

To avoid missing subscriber data, when using sp_addpublication with @allow_initialize_from_backup = N'true', always use @immediate_sync = N'true'.

I don't see this mentioned above. What is your experience with lost data on the subscriber when immediate_sync is set to false?

Thanks!

Tom


Wednesday, October 29, 2014 - 1:14:58 PM - Greg Robidoux Back To Top

Prashant congratulations on your first tip and welcome to the MSSQLTips author team.

-Greg Robidoux


Wednesday, October 29, 2014 - 12:35:45 PM - Ken Wallace Back To Top

Excellent post! Excellent. 

I have spent many long nights doing this manually, with the stress of completing everything before the end of our maintenance windows.

I'm not sure why it didn't occur to me to try to automate the process. Maybe it seemed too daunting a task? Looking at your process here, it seems doable now.

The biggest tweak I'll make is to replace the XCOPY with an SFTP push. I can SFTP from the command line with WinSCP. Our server environment doesn't allow mappeed drives between machines.

Thanks for the inspiration!


Learn more about SQL Server tools