November 18, 2010 at 8:26 am
Hi friends,
Is it possible to detach a database, which is being replicated, without dropping publications.
Thanks
John
November 18, 2010 at 8:34 am
I don't think so, but I've never tried it. Set up a test on a dev box and see if it blows up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 8:49 am
Yep, Theres no straight method. But checking whether any smart ideas.
Thanks
John
November 18, 2010 at 8:50 am
Why do you need to do this? It's possible there's another solution to the problem behind this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 8:59 am
There are huge lot of replication involved in huge lot of databases, which I'm planning to move.
So I think if I can get rid the replication rebuild, I can reduce the time window for the work.
We cannot make the DB offline or detach directly because its involved in REP.
I have an idea (Not sure its worth):
1. Make the DB RESTRICTED_USER
2. Excecute the ALTER DATABASE statement to reflect the new location.
3. Set the DB OFFLINE (Now its possible).
4. Move the file across.
5. Set the DB ONLINE (Now its up from the files at the new loc).
Not sure its worth and practical on production.
Hows it:) bright or dump?
Thanks
John
November 18, 2010 at 12:42 pm
Page9:F1 (11/18/2010)
So I think if I can get rid the replication rebuild, I can reduce the time window for the work.We cannot make the DB offline or detach directly because its involved in REP.
Have you actually tried taking a replicated database off line?
Page9:F1 (11/18/2010)
I have an idea (Not sure its worth):1. Make the DB RESTRICTED_USER
2. Excecute the ALTER DATABASE statement to reflect the new location.
3. Set the DB OFFLINE (Now its possible).
4. Move the file across.
5. Set the DB ONLINE (Now its up from the files at the new loc).
Not sure its worth and practical on production.
Hows it:) bright or dump?
Thanks
Except for step 1, this is a perfectly good way to move databases and I have a project on this weekend that moves 2 X 500GB plus a few others on 3 servers databases this way. Moving them from old NAS storage to a new SAN.
With SQL 2005 you need to be careful of the DETACH because it changes some file permissions and even database configurations. e.g. if you detach a database that hase Database Ownership Chaining enabled, this will not be enabled when you attach again. (I've tested it). The reason being a detached database doesn't exist in sysdatabases, but an offline database does.
The onlys things I would do differently are:
a) Swap step 2 & 3
b) Do a copy of the files rather than a move. That way you have a quick fallback plan.
c) If you have the space and the move allows for this just rename the drives, but keep the logical location the same. This way you can avoif the alter database to move the logical files.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 4, 2011 at 9:32 am
ive tested the above theory and works successfully with replication intact.
alter database REPTEST set restricted_user with rollback immediate;
ALTER DATABASE REPTEST SET OFFLINE;
ALTER DATABASE REPTEST
MODIFY FILE ( NAME = CMS, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\REPTEST\CMS.mdf' );
ALTER DATABASE REPTEST
MODIFY FILE ( NAME = CMS_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\REPTEST\CMS_log.ldf' );
-- Move the files at OS level
ALTER DATABASE REPTEST SET ONLINE;
alter database REPTEST set multi_user;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply