Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Moving Replicated Databases Expand / Collapse
Author
Message
Posted Monday, February 5, 2007 8:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 8:09 AM
Points: 6,779, Visits: 1,862
Biggest reason is time - detach, move, attach is fairly quick.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #342472
Posted Monday, February 5, 2007 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 5,230, Visits: 9,457

Andy

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

John

Post #342479
Posted Monday, October 8, 2007 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 9, 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
Posted Saturday, September 27, 2008 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, September 28, 2008 2:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 5,230, Visits: 9,457
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
Posted Monday, July 6, 2009 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:17 PM
Points: 236, Visits: 474
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
Posted Wednesday, August 5, 2009 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:30 AM
Points: 1, Visits: 212
Has this been tried with SQL 2005?
Post #765580
Posted Friday, November 25, 2011 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««12

Permissions Expand / Collapse