During the last days I’m working on Replication – especially with Transactional Replication. The main idea of Transactional Replication is that you have a so-called Log Reader Agent on your Publisher SQL Server database running, that is analyzing the Transaction Log File and synchronize changes on so-called Articles through a Distribution database down to your Subscription databases.

When you setup your Transactional Replication through SQL Server Management Studio, you can only initialize the Subscription database through a so-called Snapshot. A snapshot is just a point-in-time “picture” of your database, which is dumped out to a file share through bcp.exe. Imagine now that you want to publish parts of a VLDB (Very Large Database) to various Subscribers. In that case Transactional Replication must make a snapshot on the Articles that you want to replicate from your VLDB. That’s not very practical, because it can take a very long time to make that snapshot and initialize the Subscribers from that snapshot.

Fortunately there is also an option to initialize a Subscriber from a backup file, but unfortunately this option isn’t available through SQL Server Management Studio – you have to choose that option through the Replication Stored Procedures that are used in the background by SQL Server to implement the actual Replication. The idea of initializing a Transactional Replication from a backup file is not very new, because there are also other blog postings on this topic, but I wasn’t able to find a step-by-step tutorial how to implement this approach. The most important blog posting is from Chris Skorlinski (http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx). With the information provided in that posting I was finally able to do the initialization from a backup file. In this blog posting I want to show you now step-by-step how you can achieve that functionality.

In my distributed scenario Transactional Replication is running on 3 different VMs:

• SQL2008HADR1: Publisher
• SQL2008HADR2: Distributor
• SQL2008HADR3: Subscriber

Running Replication in a fully distributed scenario is more realistic, but the setup of the needed security is also a topic on its own. Fortunately Joe Sack from SQLskills.com has written a few weeks ago a very nice article how to setup security – see http://www.sqlskills.com/blogs/joe/post/SQL-Server-Pro-articlee28093e2809cGetting-Started-with-Transactional-Replicatione2809d.aspx for further details on that.

To get started I have created a completely new database on SQL2008HADR1 which acts as the Publisher for Transactional Replication:

USE master
GO

-- Create a new database that we want to use with Transactional Replication
CREATE DATABASE PublisherDatabase
GO

USE PublisherDatabase
GO

-- Create a new table that we want to replicate
CREATE TABLE Foo
(
	Col1 INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Col2 CHAR(1000) NOT NULL,
	Col3 CHAR(1000) NOT NULL
)
GO

-- Insert 1000 records
INSERT INTO Foo VALUES ('xyz', 'xyz')
GO 1000

-- Retrieve the inserted records
SELECT * FROM Foo
GO

-- Make a initial full database backup
BACKUP DATABASE PublisherDatabase TO DISK = '\\dc\temp\PublisherDatabase.bak'
GO
-- Insert another 1000 records
INSERT INTO Foo VALUES ('xyz', 'xyz')
GO 1000

-- Retrieve the inserted records.
-- The last 1000 records are not part of the initial full database backup.
SELECT * FROM Foo
GO

As you can see from the code, I have created a simple table called Foo, and inserted several records into that table. I have also created a full database backup and afterwards I have inserted several other records, which are currently stored in no backup. In the next step you have to create your Publication on the Publisher. This can be done completely through the UI provided by SQL Server Management Studio. The only thing that you don’t have to do is the creation of an initial snapshot, because we don’t need that snapshot.

In my case I have scripted out the creation of the Publication, which resulted in the following T-SQL code:

USE PublisherDatabase
GO

EXEC sp_replicationdboption
	@dbname = N'PublisherDatabase', 
	@optname = N'publish', 
	@value = N'true'
GO

-- Adding the transactional publication
EXEC sp_addpublication
	@publication = N'FooPublication', 
	@description = N'Transactional publication of database ''PublisherDatabase'' from Publisher ''SQL2008HADR1''.', 
	@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'false', 
	@enabled_for_p2p = N'false', 
	@enabled_for_het_sub = N'false'
GO

-- Add the Snapshot Agent for the Publication
EXEC sp_addpublication_snapshot
	@publication = N'FooPublication', 
	@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

-- Add an Article to the Publication
EXEC sp_addarticle
	@publication = N'FooPublication', 
	@article = N'Foo', 
	@source_owner = N'dbo', 
	@source_object = N'Foo', 
	@type = N'logbased', 
	@description = null, 
	@creation_script = null, 
	@pre_creation_cmd = N'drop', 
	@schema_option = 0x000000000803509F, 
	@identityrangemanagementoption = N'manual', 
	@destination_table = N'Foo', 
	@destination_owner = N'dbo', 
	@vertical_partition = N'false', 
	@ins_cmd = N'CALL sp_MSins_dboFoo', 
	@del_cmd = N'CALL sp_MSdel_dboFoo', 
	@upd_cmd = N'SCALL sp_MSupd_dboFoo'
GO

In that publication I’m just replicating the whole table Foo. I have not applied any filter. After you have created your Publication, you must change one setting through the UI of SQL Server Management Studio. I was not able to figure it out, if this change can be also done through T-SQL. You have to allow the initialization from backup files for subscribers. To change that setting you are doing to the local publication on the Publisher, then you go to the Properties-Window, and finally to the page “Subscription Options“. And here you are setting the option “Allow initialization from backup files” to “True“.

This setting allows the Subscribers to be initialized by a backup. In the next step I’m making a final Transaction Log Backup, so that all transactions are backed up.

Note: You need to do a backup after the Publication was configured on the Publisher. Otherwise the initialization from backup will not work!

-- Create another log backup of the Publisher database.
BACKUP LOG PublisherDatabase TO DISK = '\\dc\temp\PublisherDatabase_Tran1.trn'
GO

When you are using Replication in a distributed scenario you also have to make sure that the Distribution Agent can access your Publisher database.

In the next step you can restore the taken database backups on the subscriber, in my case SQL2008HADR3.

-- Restore the initial full database backup
RESTORE DATABASE PublisherDatabase FROM DISK = '\\dc\temp\PublisherDatabase.bak'
WITH NORECOVERY
GO

-- Restore the last Transaction Log Backup
RESTORE DATABASE PublisherDatabase FROM DISK = '\\dc\temp\PublisherDatabase_Tran1.trn'
WITH RECOVERY
GO

Now you are ready to create the Subscription. When you create a new Subscription you have to make changes on both the Publisher and also on the Subscriber. Therefore you have to execute the following T-SQL code on the Publisher (SQL2008HADR1) to create the Subscription.

USE PublisherDatabase
GO

-- Add the Subscription on the Publisher
EXEC sp_addsubscription
	@publication = N'FooPublication', 
	@subscriber = N'sql2008hadr3', 
	@destination_db = N'PublisherDatabase', 
	@sync_type = N'initialize with backup',	
	@backupdevicetype='Disk', 
	@backupdevicename='\\dc\temp\PublisherDatabase_Tran1.trn',
	@subscription_type = N'pull', 
	@update_mode = N'read only'
GO

When you are calling the stored procedure sp_addsubscription on the Publisher you can now specify that you want to initialize the Subscribers through a database backup. For that reason you set the parameter @sync_type to “initialize with backup“. You also have to supply the *last* backup that you have restored on the Subscriber through the parameter @backupdevicename. In my case this is the Transaction Log backup that I have done previously. This backup is then opened by sp_addsubscription to get the last LSN (Log Sequence Number) of the backup. This LSN is used by the Distribution Agent to replicate commands from the msrepl_transactions table to the Subscriber that have occurred afterwards.

Note: You have to call sp_addsubscription directly from T-SQL, because setting these options is not possible through SQL Server Management Studio.

When you have created the Subscription on the Publisher, you are finally ready to create it on the Subscriber (SQL2008HADR3).

USE PublisherDatabase
GO

-- Add the Pull Subscription
EXEC sp_addpullsubscription
	@publisher = N'SQL2008HADR1', 
	@publication = N'FooPublication', 
	@publisher_db = N'PublisherDatabase', 
	@independent_agent = N'True', 
	@subscription_type = N'pull', 
	@description = N'', 
	@update_mode = N'read only', 
	@immediate_sync = 0
GO

-- Add the Pull Subscription Agent
EXEC sp_addpullsubscription_agent
	@publisher = N'SQL2008HADR1', 
	@publisher_db = N'PublisherDatabase', 
	@publication = N'FooPublication', 
	@distributor = N'SQL2008HADR2', 
	@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 = 20120805, 
	@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

After completing these steps you have a fully working Transactional Replication which was initialized by a database backup instead of a snapshot. For VLDBs this can be a huge advantage instead of creating a very big snapshot that must be transferred over the network to the Subscriber, and finally applied. The drawback of this approach is that you have to restore your whole database on the Subscriber, and not only the Articles that you are publishing. Additionally you also have the whole data on the Subscriber, even when you are replicating Articles with Filter. For these reasons you have to delete the unnecessary data on the Subscriber afterwards manually.

Thanks for reading!

-Klaus