Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

  1. 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.

  2. 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.

    Caution note Caution

    To avoid missing subscriber data, when using sp_addpublication with @allow_initialize_from_backup = N'true', always use @immediate_sync = N'true'.

  3. Create a backup of the publication database using the BACKUP (Transact-SQL) statement.

  4. Restore the backup on the Subscriber using the RESTORE (Transact-SQL) statement.

  5. 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' or TAPE = '\\.\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.

  6. (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.

  7. (Optional) Start the Distribution Agent. For more information, see Synchronize a Pull Subscription or Synchronize a Push Subscription.

Community Additions

© 2015 Microsoft