Moving Replicated Databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Moving Replicated Databases

18 posts, Page 2 of 2

««1 2

Moving Replicated Databases

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Monday, February 05, 2007 8:09 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #342472

John Mitchell-245523

Posted Monday, February 05, 2007 8:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 4,434, Visits: 7,218

Andy

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

John

Post #342479

floyd99

Posted Monday, October 08, 2007 6:35 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, November 09, 2008 10:45 PM
Points: 15, Visits: 89

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

Post #408244

ck900i

Posted Saturday, September 27, 2008 4:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 20, 2011 6:44 AM
Points: 2, Visits: 11

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 allowed

how can we solve the issue ???

P.S. i'm using Microsoft Sql Server 2005 Standard

Thank You,

Post #577290

John Mitchell-245523

Posted Sunday, September 28, 2008 2:52 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 4,434, Visits: 7,218

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

Post #577379

sql_er

Posted Monday, July 06, 2009 10:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 10:08 AM
Points: 223, Visits: 424

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?

Post #747874

khone rathsachack

Posted Wednesday, August 05, 2009 9:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 1:29 PM
Points: 1, Visits: 162

Has this been tried with SQL 2005?

Post #765580

fcaglayan

Posted Friday, November 25, 2011 8:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 11, 2013 11:27 AM
Points: 9, Visits: 39

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',0
go

reconfigure
GO

It 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?

Post #1211988

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions