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.