Skip to content

Latest commit

 

History

History
69 lines (47 loc) · 5.46 KB

initialize-a-transactional-subscription-from-a-backup.md

File metadata and controls

69 lines (47 loc) · 5.46 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs
Initialize subscription from backup (Transactional)
Learn how to use replication stored procedures to initialize a Transactional publication from a backup in SQL Server.
MashaMSFT
mathoma
09/25/2024
sql
replication
how-to
updatefrequency5
manual subscription initialization [SQL Server replication]
subscriptions [SQL Server replication], initializing
initializing subscriptions [SQL Server replication], without snapshots
transactional replication, backup and restore
backups [SQL Server replication], transactional replication
TSQL

Initialize a Transactional Subscription from a Backup

[!INCLUDE 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

  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.

    [!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'.

  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.

Related content