Tuesday, March 06, 2012

setup SQL server replication using backup files

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127368
/* 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 is complete 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 the replication: EXEC sp_addsubscription @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 */

No comments: