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 11:48 am
1. Try to shut down your application/applications services.
2. Run sp_who2 to see if there are any connections to your database.
3. If some of connections are still there, run "kill yourspid" from QA untill all connections to your database disappear.
4. Run sp_detach_db to detach your database.
5. Move your MDF and LDF files to new disks.
6. Re-attach database using sp_attach_db.
Make sure you have good database backup prior performing above steps.
April 26, 2005 at 12:01 pm
the biggest issue then is the system DB's.
my main concern is Master, since it holds all the login info, and I want to avoid orphaning users. suggestions there? our thoughts were to restore those, as opposed to sp_detach_db and reattach to new box.
April 26, 2005 at 12:30 pm
I believe we have already answered your question at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=177197#bm177278
April 26, 2005 at 1:44 pm
The simple steps to restore your master database are the following:
1. Open a command window and run the code NET STOP MSSQLSERVER.
2. Still in the command window change your directory to the location where your SQL Server executable is located. (Default is C:\program files\microsoft sql server\mssql\binn)
3. Run the code sqlservr -m this will satrt SQL Server in single user mode.
4. Open a query analyzer window and run script, RESTORE DATABASE master FROM DISK = 'local path to your backup'.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply