Compartir a través de

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

  • Artículo

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 Inicializar una suscripción transaccional sin una instantánea.

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 Cómo crear una suscripción de extracción (programación de la réplica con Transact-SQL).

  7. (Optional) Start the Distribution Agent. For more information, see Cómo sincronizar una suscripción de extracción (programación de la réplica) or Cómo sincronizar una suscripción de inserción (programación de la réplica).

Vea también

Otros recursos

Copiar bases de datos con Copia de seguridad y Restaurar
Realizar copias de seguridad y restaurar bases de datos en SQL Server

Ayuda e información

Obtener ayuda sobre SQL Server 2005