Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2009-06-10 : 05:09:23
|
| [code]/*Steps to set up replication using backup files instead of snapshots1. 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 True3. 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 complete4. 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 here9. Enable the "Distribution clean up: distribution" job again */[/code]- Lumbago |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-14 : 11:33:50
|
| Good job Lumbago. This is how I have setup Transaction Replication for 250 GB Databases. Also You can Enable constraints after replication is setup in subscriber. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-06-15 : 04:05:50
|
| Thanx sodeep, I did consider enabling the constraints again but I figured that since the constraints are enabled in the publishing database it won't cause any harm to leave them disabled on the subscribers.- Lumbago |
|
|
|
|
|
|
How to set up replication from backup files