Moce SQL to a different drive

[gporter416]

I'm not an expert but what i'm trying to do is move to move a couple of SQL db from from drive to another so SQL will look to the DB on the new drive. If I try and restore and set the path to a different drive I get a ODBC-SQL State 420000 Access denied as it can't get exclusive access.

 

[mutley1]

Hi gporter,

An easier way is to detach the database, move the files on the server then attach them again. Check out BOL.

HTH,

M.

 

[Catadmin]

If you're trying to move data files on the same server to a different logical drive, then mutley1 is correct. Detach, move the file and re-attach the db.

If you're trying to move the database to another SQL Server completely, please describe how you're "moving" the db.

Are you backing it up and then trying to restore or are you trying to copy the data & log file and move them over to the new server? If the later, then it won't work. You can't copy data & log files while the database is in use and attached to the server.

Well, you can, but all you're liable to get is an error message like the one you have or a corrupted database.

If neither of these posts answers your question, please post the details of your move process so we can help you better.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mutley1]

Porter,

The other thing that you might be doing is creating the "blank shell" DB and clicking on it in Enterprise manager, then trying the restore - it usually complains that someone is using it (namely yourself!). If you have created a blank DB then just click on "databases" in EM and then RIGHT click on the shell you created and then select restore.

If not, then you can always use T-SQL to restore the DB.

Make sure the DB you want to create does not exist in EM then use the following code to restore from backup - you'll need to know the logical names for the data / log files:

RESTORE DATABASE mydb
FROM DISK = 'D:\Mutley1\blah\mydb_datetime.BAK'
WITH RECOVERY
MOVE 'mydb_logical_data_name' TO 'd:\LOCATION\mydb.mdf',
MOVE 'mydb_logical_log_name' TO 'd:\LOCATION\mydb.ldf'

HTH,

M.