SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Replicated Databases


Moving Replicated Databases

Author
Message
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25543 Visits: 2746
Biggest reason is time - detach, move, attach is fairly quick.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34901 Visits: 16662

Andy

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

John


floyd99
floyd99
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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 Smile
ck900i
ck900i
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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,
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34901 Visits: 16662
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
sql_er
sql_er
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 562
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?
khone rathsachack
khone rathsachack
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 283
Has this been tried with SQL 2005?
fcaglayan
fcaglayan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 55
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search