April 26, 2005 at 11:41 am
we're moving to new box. trying a coupla dry runs to move .mdf and .ldf from old disk device to new disk device and restore from new disk device using this scipt:
use master
restore database <database name>
from DISK = 'new disk device location, full backup'
with replace
query ananlyzer says it can't obtain exclusive access because DB is in use.
I've tried taking it offline, I've tried setting to single user mode, keep getting same results. HELP!
April 26, 2005 at 12:15 pm
Is SQL EM open while you are attempting to restore in QA and sql server is in single user mode? If so, close SQL EM. If not, then see articles below:
April 26, 2005 at 12:36 pm
search this site for usp_killusers stored procedure and install it in master, run this in QA before the restore statement and all should go fine.
April 26, 2005 at 1:35 pm
You're not trying to restore Master are you? In many cases where a restore gives that error, its the user doing the restore that blocks it. Be absolutely sure that you are connected to a database other than the one you are attempting to restore.
Next, if you are trying to restore master, you need to thoroughly research restoring Master in Books Online. Its not like restoring any other database.
Finally, you said you copied the mdf and ldf files, and then attempted to restore with your script. Those two statements are really mutually exclusive. You would not normally copy mdf/ldf and then attempt a restore. You aren't considering the mdf/ldf files as your backup, are you?
Normally, moving a database from server1 to server2 would involve one of two processes -
1. Take database offline on server1. Copy mdf/ldf files to new server. (Then you can bring the database back online on server1 if desired.) ATTACH the database on the new server using the stored procedure sp_attach_db.
or 2. Perform a full backup of the database on server1. Copy the backup file to server2. Perform a RESTORE DATABASE statement specifying the backup file (as you described in your post).
With either of these, if you DON'T move the Master database, you will need to reapply security on server2.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply