Interesting !

I have to move data files for a merge replicated database next week, and in testing just came across this rather annoying problem in Sql2000. I have a feeling in 2005 it would be a different story, as BOL for 2005 states you can do an ALTER DATABASE MODIFY FILE to change the file locations, but 2000 doesn't allow this. (Am I correct?)

I just tried Andy's trick and it seemed to work on my test publication.

Scripting out the publication and recreating it might seem easy to some of you, but actually it's a pain in the &$*%. When you do that, it renders the subscriptions useless, so you then have to visit each and every subscriber computer, drop the subscription database and recreate it. It's not difficult, and only takes 5 minutes, but then the snapshot has to be pulled down as well so that's another 5-10 minutes per machine. All our subscriptions are laptop computers - trying to get access to them is hard enough.

So, this little trick is a life saver.

I'm not a DBA by any stretch but I don't see much harm in changing one little flag temporarily... unless someone can give me a really good reason not to do it this way, it saves me a truckload of effort and time. Likewise, backup/restore takes time.

cheers!

Dave 🙂