By: Prashant Shekhar | Comments (5) | Related: > 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.
About the author
Prashant Shekhar is employed as a DBA with BioTelemetry; specializing in HA solutions comprised of SQL Clustering, Replication and Log Shipping.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
