Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

  • Set up the distributor, publication, and subscription
  • Turn off distribution agent
  • Set the publisher to "sync with backup"
  • Backup the publisher, full then log
  • Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on)
  • Turn off "sync with backup"
  • Restore the full and tran log backups to new subscriber db
  • Create subscriber stored procs in subscriber
  • Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike



Answer this question

Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

  • Richard Weir

    OK, I just did a search and came across this:

    http://support.microsoft.com/default.aspx scid=kb;en-us;320499

    However this method still requires a brief time in single user mode (ie killing all connections), whereas my method doesn't.  I just don't like that my method involves deleting the MSrepl_ tables...


  • Do I really need a snapshot (to initialize transactional replication, in SQL2000)?