Initialize a Transactional Subscription from a Backup
Applies to:
SQL Server
Although a subscription to a transactional publication is typically initialized with a snapshot, a subscription can be initialized from a backup using replication stored procedures. For more information, see Initialize a Transactional Subscription Without a Snapshot.
To initialize a transactional subscriber from a backup
For an existing publication, ensure that the publication supports the ability to initialize from backup by executing sp_helppublication (Transact-SQL) at the Publisher on the publication database. Note the value of allow_initialize_from_backup in the result set.
If the value is 1, the publication supports this functionality.
If the value is 0, execute sp_changepublication (Transact-SQL) at the Publisher on the publication database. Specify a value of allow_initialize_from_backup for
@property
and a value of true for@value
.
For a new publication, execute sp_addpublication (Transact-SQL) at the Publisher on the publication database. Specify a value of true for allow_initialize_from_backup. For more information, see Create a Publication.
Warning
To avoid missing subscriber data, when using sp_addpublication or sp_changepublication with
@allow_initialize_from_backup = N'true'
, always use@immediate_sync = N'true'
.Create a backup of the publication database using the BACKUP (Transact-SQL) statement.
Restore the backup on the Subscriber using the RESTORE (Transact-SQL) statement.
At the Publisher on the publication database, execute the stored procedure sp_addsubscription (Transact-SQL). Specify the following parameters:
@sync_type
- a value of initialize with backup.@backupdevicetype
- the type of backup device: logical (default), disk, or tape.@backupdevicename
- the logical or physical backup device to use for the restore.For a logical device, specify the name of the backup device specified when sp_addumpdevice was used to create the device.
For a physical device, specify a complete path and file name, such as
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\BACKUP\Mybackup.dat'
orTAPE = '\\.\TAPE0'
.(Optional)
@password
- a password that was provided when the backup set was created.(Optional)
@mediapassword
- a password that was provided when the media set was formatted.(Optional)
@fileidhint
- identifier for the backup set to be restored. For example, specifying 1 indicates the first backup set on the backup medium and 2 indicates the second backup set.(Optional for tape devices)
@unload
- specify a value of 1 (default) if the tape should be unloaded from the drive after the restore is complete and 0 if it should not be unloaded.
(Optional) For a pull subscription, execute sp_addpullsubscription (Transact-SQL) and sp_addpullsubscription_agent (Transact-SQL) at the Subscriber on the subscription database. For more information, see Create a Pull Subscription.
(Optional) Start the Distribution Agent. For more information, see Synchronize a Pull Subscription or Synchronize a Push Subscription.
See Also
Copy Databases with Backup and Restore
Back Up and Restore of SQL Server Databases