log shipping restore & recovery test

I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following

RESTORE DATABASE database_name WITH RECOVERY

but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?

Try as below,

Code Snippet

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then restore the db as,

Restore database dbname with recovery

just disable the jobs in primary server no need to delete the maintenance plan it should work fine !

you can refer the articles related to log shipping in,

http://sql-articles.com/articles/lship/lship.htm

http://sql-articles.com/articles.php

For failover in SQL 2005, refer

http://sql-articles.com/articles/lship/fail-lship.htm

refer the link for

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

Thanxx

Deepak

|||

the alter database command was successful HOWEVER, the restore still did not work because of the same reason...

Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

|||

you need to ensure that you dont have any connections to the db accidentally ! !

just go to SSMS or enterprise manager and type sp_who2 and see the dbname column and see the corresponding spid column if the value is less than greater than 50 type as follows,

kill spid value and then perform the restore it will work ! the problem is someone is accessing the db

Thanxx

Deepak

|||

I did the sp_who2 and saw two connections, one was the EM and one was the query analyzer... I killed the EM but when I tried the restore on query analyzer, it still gave me the same error about not being able to get exclusive use...

|||

ah... I changed the db in the drop down to point to the master and then did the recovery... it worked! thanks....

sql