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
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' GO
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.
- 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.
- 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.
- 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 @Publication=mydata_trans_repl, @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.
- Take a transaction log backup and apply it to the subscriber database. This restore
should be with RECOVERY so that database will be accessible.
- 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:
This is because you will need to apply
another log backup to this database.
Let us assume that subscription
database is myData_Subs.
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 – http://msdn.microsoft.com/en-us/library/ms147834.aspx
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
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.
you have taken the log backup of the replicate database after you enabled the