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

  • Article

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 Initialisation d'un abonnement transactionnel sans capture instantanée.

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\MSSQL.1\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 Procédure : créer un abonnement par extraction de données (programmation Transact-SQL de réplication).

  7. (Optional) Start the Distribution Agent. For more information, see Procédure : synchroniser un abonnement par extraction de données (pull) (Programmation de la réplication) or Procédure : synchroniser un abonnement par envoi de données (push) (Programmation de la réplication).

Voir aussi

Autres ressources

Copie de bases de données avec la sauvegarde et la restauration
Sauvegarde et restauration de bases de données dans SQL Server

Aide et Informations

Assistance sur SQL Server 2005