Exclusive access could not be obtained because the database is in use.

Can anybody provide me script for that please?

I have a better tip than all of these...

Right mouse click on the database, click All Tasks, click detach database, click the button that says clear. No click Cancel (you don't really want to detach the database).

This is how I do it... it's very easy as long as you don't accidentally detach the database. If you do, just reattach it.

I wrote a cursor but it shows the error that error at something.



CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC       sp_who

DECLARE @spid INT    
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
      SELECT       spid
      FROM      #TmpWho
      WHERE       dbname = 'mydb'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
 KILL @spid --SELECT @spid works fine here
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho

Error:
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@spid'.

Thank you, Atlanta_Mike
Well I am requesting T-SQL. How can we do what you suggested in T-SQL please? The reason is I would like it auto mated.

Ahhh... I see...

I have something... let me find it and I'll post.

If someone can fix my cursor above will solve my problem too.

Sorry like this:

ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Then just issue the RESTORE commands..

This way the user becomes in single_user by the process that is doign the restore, the restore runs and when the database is restored it is back in multi user mode.

MikeWalsh,
That is great suggestion.
Would you pls suggest how can I make it for multi user after restore.

Regards,

it becomes multi user after the restore by default.

Mike I like that... I've never though of that one. I'll have to remember that one if I ever decide to move a database through a arestore process.

Nice

Because it forgets that it was in Single User and reverts back to the state it was in at the time of the backup..

Give it a shot and you will see.

The only downside is any users work is interrupted and stopped, so I use this with proper warning.. YOu can also lookup he ROLLBACK command and see options for delaying like ROLLBACK AFTER 5 (minutes).. but immediate is what I use

1) Thank you MikeWalsh, I will try it now and will get back to you.
2) Altanta_mike
The modification in your script which works fine is as follows:

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC      sp_who

DECLARE @spid INT  
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
     SELECT      spid
     FROM     #TmpWho
     WHERE     dbname = 'mydb'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(4))
    EXEC(@tString)

FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho

MikeWalsh
 "The only downside is any users work is interrupted and stopped, "
That is perfect. There should not be any user on the DATABASE at that time. That is the requirement. So if they are, I need to kick them out any way.

Question?
Do I need to have GO between
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO --is it required?
RESTORE

I wouldn't even go through the KILL cursor.. There is no need to do that when you just rollback immediate, it does the same thing but a little more gracefully (both on you and the processes you are rolling back.. less coding and it forces a rollback of any non-committed transactions)

It is not required, that is just how I execute it.. I have a script to restore several databases to a QA server and I separate each block of commands by a GO

Thanks MikeWalsh,

Reason I was asking cause everytime I put GO in my Schedulled Job it says... it does not like it. So sometime I put them in different Steps but I just wanted to have them in one step only.

Will report back in 10 min.

All Good Guys.
Thanks for everything.
MikeWalsh your answer is magical :)