Replication Without Creating a Snapshot

In replication, the standard way of creating a publisher is
to create a publication with a snapshot (which is what all the documentation recommends).

However there are a few problems with this method:

  • Creating the snapshots and then applying them is time consuming.
  • Snapshots will consume disk space.

There is an alternative to this, where you can restore
the database into a separate database which will then become the subscription
and then create the subscription with sync_type set to none as
shown below:

use [MyData] 

exec sp_addsubscription @publication
= N'myData_Subs', @subscriber = N'DINESHAWIN7',
@destination_db = N'myData_Subs', @subscription_type =
N'Push', @sync_type = N'none',
@article = N'all', @update_mode = N'read only',
@subscriber_type = 0 

exec sp_addpushsubscription_agent @publication = N'myData_Subs',
@subscriber = N'DINESHAWIN7', @subscriber_db = N'myData_Subs',
@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'


However, the problem with this approach is that there will be
data loss between the backup time and the time of adding the subscription (unless you take the drastic step of stopping all applications).

There is another way of doing this by syncing with the
database backup, this feature is available from SQL Server 2005.

Let us assume that you have a database in production
from which you need to create a subscription. In production, typically you will
have full backups and transaction log backups scheduled.

Obviously, it will be better to disable those backups. This
is not a requirement and if you need your backups to run continuously let them proceed, but by disabling them you will definitely make the process easier.

  1. Using full/diff and log backup build your subscription database. This is the
    standard procudure for building a database using backups. Make sure you apply
    the last database backup with the NORECOVERY option as shown below.
  2. This is because you will need to apply
    another log backup to this database.

     Let us assume that subscription
    database is myData_Subs.

  3. Create the publication. You can use the wizard for this but the important
    thing to remember is that you should not enable the snapshot.

    In this example the publication name is mydata_trans_repl.
  4. Enable the publication to allow initialization from a backup. In the
    replication properties, under subscription options, set the Allow
    initialization from backup files
    option to true as shown below:

    Similarly you can do this using a TSQL script:

    EXEC sp_changepublication  
      @property = N'allow_initialize_from_backup',  
      @value = True 

    This has to be a separate option as you
    will not be given this option using the wizard.

  5. Take a transaction log backup and apply it to the subscriber database. This restore
    should be with RECOVERY so that database will be accessible.
  6. Add the subscription using the below TSQL. Note the sync_type, backupdevicetype and backupdevicename options which are for syncing with the database backup :
    exec sp_addsubscription
    @publication = N'mydata_trans_repl',
    @subscriber = N'DINESHAWIN7',
    @destination_db = N'myData_Subs',
    @subscription_type = N'Push',
    @sync_type = N'initialize with backup',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0,
    @backupdevicetype ='disk',
    @backupdevicename ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\myData_l.bak'

    In the @backupdevicename you need to provide the
    last backup used to restore the subscriber that was taken after the publication
    was created. Once you create this, data between the time of the last backup and the latest backup will be synced as shown from the replication monitor:

So you can see that you will be able to create a
replication without any down time.

If your database backup uses a password,
there are parameters to set –
will give you all the parameters you need for this option.


The Main problem with this approach is the subscription will have all
the database objects such as stored procedures, views, functions and tables
etc. So you will need to manually drop those objects.

Also, if you have filtered articles where you are replicating a
subset of the rows, you need to delete the unnecessary rows manually.


There may be a few errors you encounter during this operation:

The specified publication does not allow subscriptions to
be initialized from a backup. To allow initialization from a backup, use
sp_changepublication: set ‘allow_initialize_from_backup’ to ‘true’.

Obviously, this means that you have not enabled the
publication to allow initialization from backup.  You need to enable the setting as
shown above.

The transactions required for synchronizing the nosync
subscription created from the specified backup are unavailable at the
Distributor. Retry the operation again with a more up-to-date log,
differential, or full database backup.

Make sure
you have taken the log backup of the replicate database after you enabled the


No comments yet... Be the first to leave a reply!