SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Moving Replicated Databases / Latest Posts

RE: Moving Replicated Databases

fcaglayan — Fri, 25 Nov 2011 08:25:22 GMT

Andy That is just what I was looking for and worked for me at test system for Merge replication.But there is just 1 thing that I can not set it back.I am trying to disable to updating system tables :exec sp_configure 'allow updates',0goreconfigureGOIt seems updated but then ı try to rerun the code update sysdatabases set category=0 and it works again.so seems like once you set enable updating there is no turning back?what may I do to disable it back.isn't it a bit risky?

RE: Moving Replicated Databases

khone rathsachack — Wed, 05 Aug 2009 09:20:27 GMT

Has this been tried with SQL 2005?

RE: Moving Replicated Databases

sql_er — Mon, 06 Jul 2009 10:25:59 GMT

We just moved a replicated database from EMC to 3 Par yesterday. All we had to do was stop replication, shut down the server, copy the database files to the drives with same names, and start the server again and then replication.I am assuming that the above approach is necessary if you would like to move a replicated database while the database server is still running - but if you can shut it down, then none of this is necessary.Is that correct?

RE: Moving Replicated Databases

John Mitchell-245523 — Sun, 28 Sep 2008 02:52:59 GMT

That's another limitation of Andy's method from which mine doesn't suffer - you can't make updates directly to the system tables on SQL Server 2005.John

RE: Moving Replicated Databases

ck900i — Sat, 27 Sep 2008 04:48:55 GMT

actually, i have the same problem, i have 2 databases that are on a drive and due to space problem, i need to move them into a new Drive, the 2 databases are replicated into 3 sites,i was following your steps but i had an error when executing "update sysdatabases set category=0 where name='myDBName' : Ad hoc updates to system catalogs are not allowedhow can we solve the issue ???P.S. i'm using Microsoft Sql Server 2005 Standard Thank You,

RE: Moving Replicated Databases

floyd99 — Mon, 08 Oct 2007 18:35:06 GMT

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 :)

RE: Moving Replicated Databases

John Mitchell-245523 — Mon, 05 Feb 2007 08:33:00 GMT

Andy

Surely not if that involves dropping and recreating publications, or hacking system tables? Backup and restore requires neither of those.

John

RE: Moving Replicated Databases

Andy Warren — Mon, 05 Feb 2007 08:09:00 GMT

Biggest reason is time - detach, move, attach is fairly quick.

RE: Moving Replicated Databases

John Mitchell-245523 — Mon, 05 Feb 2007 02:12:00 GMT

Andy

Why not just back up the database and restore it to the new location? You don't have to touch the system tables then.

John

RE: Moving Replicated Databases

Roust_m — Sat, 03 Feb 2007 04:45:00 GMT

"That's a nice trick, wish I had known it a few months earlier! Andy http://www.sqlservercentral.com/columnists/awarren/ Andyhttp://www.endtoendtraining.com/"

RE: Moving Replicated Databases

Andy Warren — Fri, 02 Feb 2007 06:01:00 GMT

Its an older article, Steve sometimes will rerun one that he thinks is interesting on Friday.

RE: Moving Replicated Databases

Roust_m — Thu, 01 Feb 2007 20:06:00 GMT

"I ran into this a couple years ago..."

Nice solution, but it was discussed on this forum three years ago and you may remember this topic...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=14226#bm70809

RE: Moving Replicated Databases

Andy Warren — Mon, 20 Mar 2006 16:53:00 GMT

Travis,

I understand your position, but its just a matter of mitigating the risk isnt it? Aside from that, what do you think could go wrong with this approach?

RE: Moving Replicated Databases

Atul Varshney — Mon, 20 Mar 2006 01:12:00 GMT

good

RE: Moving Replicated Databases

Atul Varshney — Mon, 20 Mar 2006 01:06:00 GMT

It is a good Solution for Moving Replicated Databases

Atul Varshney

RE: Moving Replicated Databases

Rodrigo Acosta — Fri, 03 Feb 2006 20:07:00 GMT

Well, I can be help full in cases you have very big publications and re applying the snapshot takes a very long time. I would really test it in my databases before trying it out in a production environment, but supose that something goes wrong, you can always recreate the publications again.

RE: Moving Replicated Databases

talltop — Wed, 01 Feb 2006 08:08:00 GMT

Clever, but I don't recommend doing this at all. You are really risking getting things really screwed up! You are better off just scripting out replication including the jobs, removing it totally (sp_removedbreplication and the jobs), detaching, attaching, and just running the replication script again..It is a little longer, I know but alot safer in the long run..The problem with clever little shortcuts like this is they can fill volumes with what they don't tell you can go wrong! Don't gamble with your databases with cute little workarounds like this. You will probably regret it sooner or later. Stick to what has been tried, true, and tested. DBA's that start experimenting with things like this usually find themselves looking for another job...trust me I have seen it far too often.

Travis Lee Alltop

Sr. SQL Server DBA

Moving Replicated Databases

Andy Warren — Mon, 30 Jan 2006 14:59:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/movingreplicateddatabases.asp