September 19, 2003 at 6:35 am
I would like to move a 10GB database to a new hard drive that has just been added to our SQL 2000 server. The database is usually being accessed by several applications. I was planning to stop and restart SQL server in single user mode(sqlservr.exe -m -c), detaching the database, copying the database file to the new drive (leaving the log file), and reattaching. Is there any problem with this procedure? I am a new administrator and usually work from Enterprise Manager menu items as opposed to scripts. Will I miss necessary options using EM? Would the Copy Database Wizard work instead ?(with move option?). Thanks for your help.
September 19, 2003 at 6:58 am
Shouldn't cause you any problems. But use MOVE instead of COPY.
I've done the same thing with a 200+ GB database. You don't even have to stop and restart services. In Enterprise Manager, when you right click on the Database and chose Detach, the popup window will let you know how many connections there are and also let you 'kill' the connections.
There's also an option in Enterprise Manager (right click on database, select Properties, go to Option tab) to set the database to SINGLE-USER mode.
-SQLBill
September 19, 2003 at 8:37 am
Thanks for your quick reply. I guess your saying that either the detach/file copy/attach method OR the Copy Database Wizard with "move" option should work, but the Copy Database Wizard will not require stopping and restarting the SQL server. But will the Copy Database Wizard require going into single user mode? Thanks.
September 19, 2003 at 10:51 am
I didn't say anything about COPY DATABASE WIZARD as I have never used it.
I think you misunderstood my comment about MOVE and COPY. You said you were going to COPY the file to the new location. That means you are going to have to copies. One in the original location and one in the new location. Then you will have to delete the original copy. I'm saying 'don't copy it, move it to the new location. (Drag and drop or just use the move file command).
-SQLBill
September 22, 2003 at 5:38 am
My mistake. Thanks again.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply