Upgrading 2000 to 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Upgrading 2000 to 2005

Hello folks! A colleague asked me a very interesting question. Currently a friend of his works for a company with SQL Server 2000 transactional replication. They have the source database at Head Office, and it replicates to 10 child servers. They want to upgrade to SQL Server 2005. What they intend to do is backup the database, install 2005, and then restore the backup. They’ll then be running some upgrade scripts. My question is as follows: When they set up the new replication on SQL Server 2005, will they have to redo the snapshot, etc.? Or is there a way they can just continue from where they left off, i.e. replicate just changed / added records to the child servers. The problem is they only have 256k line, and to have to do a snapshot again of a 29GB database to the child servers will take forever! Thank you in advance for any assistance in this regard.
Then can configure the snap shot not to copy the existing data…
Check publication properites/Artilcles/properties/snapshot …
Mohammed U.
restoring replicated database is always tricky. u have to synchronies system databases also to get the replication in the earlier state after restoration. so u have to backup master,msdb at publisher ,distributor, subscriber also. When u are upgrading , i don’t think this system database synchronisation is possible. I have never done that and i am little Skeptic about it. Madhu

I suggest to restore to database and redefine the replication in SQL 2005 rather than continuing the same. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
If you have lot of publications and subscribers you can script the replication and use that to create in 2005. Breaking Changes in SQL Server 2005 Replication
http://msdn2.microsoft.com/en-us/library/ms143470.aspx How to manually remove a replication in SQL Server 2000 or in SQL Server 2005
Mohammed U.