Categories: ReplicationSQL ServerSQL Server Administration

SQL 2008 Transactional Replication and initializing from a backup file

In some environments,running Fresh snapshot will take ages to synchronise the subscribers with publishers. Even if it is done in low activity hours, it will run into business hours because of time takes to take snapshot, copy, restoring snapshot, synchronising transactions again.
There is way in which the replication can be setup by initialising using a backup in SQL Server 2008. This new feature is very useful in environments where it takes a lot of time to run a fresh Snapshot/Re-initialise the replication.
I have followed a following set of steps in setting this up and testing:

Step No	Steps Followed
1	Set up the Distributer (This applies to fresh Replication where a database and share to be setup)
2	Set up Publisher on source server ( Do not tick checkbox for snapshot/Initialising).
3	Enable the Publication properties to allow “initialize from backup”. (Right-click on the publication properties in subscription options)
4	Disable the distribution cleanup agents(Under SQL agent jobs).
5	Make a Full backup of the database. Keep a local copy as you will need it later.
6	Copy database to other site.
7	Restore database with any name
8	Create pull subscription on the destination server from GUI selecting the publication created above.
9	Check status of Replication in Replication monitor. Drill down to publisher (this needs to be added if it’s not there)
10	After it’s done synchronising
11	If needed delete all objects which are not part of Replication and do a cleanup to clear some space.

This is very straight forward process but remember: 1. Please follow the steps in the same order. 2. Always try to simulate this in test/development environment first and document the procedure. 3. Keep the logins needed for Replication Setup ready. 4. Do not forget to give folder permissions on Snapshot folder to logins used for Replication.