Initialize SQL Server replication using a database backup

Problem

You need to configure transactional replication for a very large SQL Server database. Instead of running the snapshot agent to initialize the subscriber, in this tip we look at how you can initialize the subscriber from a SQL Server backup.

Solution

We need to be cautious when running the snapshot agent job in SQL Server replication when databases are very large, because of the overhead of exporting, transferring and importing the data into the subscriber. An alternative approach to initialize the subscriber database is to use a SQL Server database backup.

Snapshot Agent Settings

To do this, when setting up replication we need to make sure that we do not create the snapshot immediately while configuring the new publication. The below screen shot shows these items in the replication publication wizard in SQL Server Management Studio and shows that both options should be left unchecked.

Allow Initialization From Backup Files

After the publication is created right click on your publication and select Properties. (In SSMS go to Replication > Local Publications > your publication and right click). On the properties window go to Subscription Options as shown below and change the “Allow initialization from backup files” setting to True.

Backup Database on Publisher

After the above settings are made, we need to create a backup of the published database. In our example, the published database is R_PUB and the subscriber database is R_SUB.

Here is the backup command or this can be done via SSMS.

/*Take backup of publisher database*/

backup database R_PUB to disk =’C:\R_PUB.bak’ with init, stats

Restore Database on Subscriber

Once the backup is done, we need to transfer the backup to the subscriber server and restore the database. Again this can be done using T-SQL as shown below or SSMS.

/*Restore the backup of publisher on subscriber*/

use master

go

restore database R_SUB from disk =’C:\R_PUB.bak’

with move ‘R_PUB’ to ‘C:\MSSQL\DATA\R_SUB.mdf’,

move ‘R_PUB_log’ to ‘C:\MSSQL\DATA\R_SUB_log.ldf’,

replace,

stats

Create Subscription Using T-SQL

After the backup has been restored on the subscriber, on the publisher we need to add a subscription to this publication.

Note the last three parameters in the command below this is what tells SQL Server how to initialize the subscriber:

• @sync_type – using a backup
• @backupdevicetype – the backup was created on disk
• @backupdevicename – the name of the backup file that was created

/*At the publisher, run the following command */

USE [R_PUB]

GO

EXEC sp_addsubscription

@publication = R_PUB,

@subscriber = ‘Enter your subscriber server name here’,

@destination_db = R_SUB,

@sync_type = ‘initialize with backup’,

@backupdevicetype =’disk’,

@backupdevicename = ‘C:\R_PUB.bak’

Once you run above command, you will see a message similar to the below message.

Validating Snapshot Was Not Created

With this approach transactional replication is setup without running the snapshot agent job and you will also notice that the default snapshot folder remains empty since a snapshot was not generated.

Next Steps

Before implementing these steps in a production environment, it is important that you schedule it during a proper change window so that you would be able to configure this setup without any issues. We need to consider this point as there would be other replication maintenance jobs that would be running in the background like the “distribution clean up” job which will periodically clear the distribution database of transactions to replicate.

• Consider configuring the initial snapshot for replication through backup especially for very large databases if you feel there might be performance issues
• Analyze the space availability on both the publisher and subscriber servers as you need space for the full backups
• Plan a suitable change window for performing these steps on a production environment
• Be aware of the space impact of running the snapshot agent job
• Refer to other related tips on replication to get familiar with the concepts

Mohammed Moinudheen

Mohammed has been a SQL Server DBA since 2005 and has had the opportunity to work on different versions of SQL Server starting from SQL Server 2000 with exposure to Sybase as well.  He is based in Wellington, New Zealand and works at Datacom Systems Ltd, as a SQL Database Administrator. He can be reached at: moinu@live.com.

• MSSQLTips Awards: Author of the Year Contender – 2016, 2018-2021 | Achiever (75+ tips) – 2021