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
Expand Minimize

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

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 Initializing 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 How to: Create a Publication (Replication Transact-SQL Programming).

  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\MSSQL10.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 How to: Create a Pull Subscription (Replication Transact-SQL Programming).

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

Community Additions

© 2015 Microsoft