January 7, 2009 at 4:12 am
Hello,
Can i move my SQL 2005 production Database to another location, while database is in use ??
I can move by applying sp_detach_db and then sp_attach_db, But i want to do this while DB remains online all the times
cheers,
Muhammad
January 7, 2009 at 4:56 am
There's no way to move a database while it's online.
The two ways to move a DB are the detach/attach method you have, or to do an ALTER DATABASE ... ALTER FILE .. MOVE. Once you've done that, take the database offline, move the files and then bring the DB back online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 5:19 am
I have used mirroring to do this in a web asp.net app with minimal downtime - you will loose a few connections but it is only for a few seconds.
What you do is set up mirroring - change the web.config to include the failover partner - restart the app pool so new connections get the new settings, after a while - failover to the mirror - remove the failover partner from the web.config - remove the mirroring.
This relies on a) you being able to change the client apps or web page without affecting service and b) being able to loose a few connections.
Obviously you need to test, test, test and probably test again in your environment!
ed
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply