Steps to set up replication using backup files instead of snapshots
1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.
2. Once the wizard has finished, right-click the new publication, choose Properties
and make sure that the "Allow initialization from backup files" is set to True
3. Disable the "Distribution clean up: distribution" job to make sure that no
commands are deleted from MSrepl_commands before the entire backup/restore operation
4. Create a full database backup to disk and make sure that the file is available for the
publisher until the entire replication setup is finished.
5. Transfer the backup file to the subscriber server and restore it there. If you do
regular backups to disk make sure to use one that was taken *after* the publication
was created. Also restore trans log backups taken after the full backup. The last backup
should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves
you time in the syncing process when the replication is being initialized.
6. Disable all triggers on the subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
7. Disable all constraints on subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'F:\backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
publishing server. If you restored trans logs also the last translog file is what you
need to put here
9. Enable the "Distribution clean up: distribution" job again