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

  • Artikel

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 Initialisieren eines Transaktionsabonnements ohne 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\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 Vorgehensweise: Erstellen eines Pullabonnements (Replikationsprogrammierung mit Transact-SQL).

  7. (Optional) Start the Distribution Agent. For more information, see Vorgehensweise: Synchronisieren eines Pullabonnements (Replikationsprogrammierung) or Vorgehensweise: Synchronisieren eines Pushabonnements (Replikationsprogrammierung).

Siehe auch

Andere Ressourcen

Kopieren von Datenbanken durch Sichern und Wiederherstellen
Sichern und Wiederherstellen von Datenbanken in SQL Server

Hilfe und Informationen

Informationsquellen für SQL Server 2005