How to: Initialize a Transactional Subscription from a Backup with Multiple Backup Files

Below is a slight modification to the How to: Initialize a Transactional Subscription from a Backup steps to handle Publisher database backups to multiple BAK FILES.

1)       Execute sp_addpublication (Transact-SQL) at the Publisher on the publication database. Specify a value of true for @allow_initialize_from_backup = N’true’ and @immediate_sync = N’true’

2)       Create a backup of the publication database using the BACKUP (Transact-SQL) statement.

a.        Full Database Backup to multiple BAK files

b.       Start copy of the multiple BAK files to the Subscriber  (may take hours to perform)

c.        Transaction Log backup to TRN file

d.       Copy the Transaction log backup to the Subscriber

3)       Restore the backup on the Subscriber using the RESTORE (Transact-SQL) statement.

a.        Restore the Fullback from the BAK files using “no recovery”

b.       Restore the Transaction log TRN backup file using “with recovery”

4)       At the Publisher on the publication database, execute the stored procedure sp_addsubscription (Transact-SQL) with the following changes

5)       Modify the parameters:

a.        @sync_type = N’initialize with backup’

b.       Add new parameters: 

 i.      @backupdevicetype = ‘Disk’

ii.      @backupdevicename = ‘C:\BACKUP\MyLogBackup.TRN’.

WATCH OUT:

 For PUSH Distribution Agents, the TRN location (step 5.2) must be accessible from the Publisher by either

1) point to the original TRN file on the Publisher
— or —
2) use UNC mapping to the TRN on the Subscriber.

If using @sync_type = N’initialize with backup’ what do you plan to do about the user-defined Constraints, Identities, and Triggers?  You have 2 options.

1. Enable the “NOT FOR REPLICATION setting on the Publisher Constraints, Identities and Triggers before you create the Publication. Click HERE for details.
2. Disable Constraints, Identities, and Triggers AFTER you restore on the Subscriber but before you enable the Subscription.

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"