How to obtain exclusive access via SQL

[fatherjack]

Hi,

i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:

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

how do i gain this Exclusive use via an SQL statement please?

[MAK]

Create this procedure in Master Database
and execute this proc before restoring any database.

This will kick out all the users from a particular database that you are about to restore.


use master
go

--TYPE PROCEDURE
--AUTHOR MAK MAK_999@YAHOO.COM,MUTHUSWA@PCCONNECTION.COM
--DATE WRITTEN 4/19/2000
--PROJECT MAINTENANCE
--OBJECTIVE PROCEDURE TO KILL PROCESS FOR A GIVEN DATABASE

CREATE PROCEDURE USP_KILLPROCESS @dbname varchar(100) AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @KILL_ID int
DECLARE @QUERY VARCHAR(320)

DECLARE KILLPROCESS_CURSOR CURSOR FOR
SELECT A.SPID FROM SYSPROCESSES A JOIN
SYSDATABASES B ON A.DBID=B.DBID WHERE B.NAME=@DBNAME


OPEN KILLPROCESS_CURSOR
FETCH NEXT FROM KILLPROCESS_CURSOR INTO @KILL_ID

WHILE(@@FETCH_STATUS =0)
BEGIN

SET @QUERY = "KILL "+ CONVERT(VARCHAR,@KILL_ID)

EXEC (@QUERY)
FETCH NEXT FROM KILLPROCESS_CURSOR INTO @KILL_ID
END

CLOSE KILLPROCESS_CURSOR
DEALLOCATE KILLPROCESS_CURSOR

--USAGE
--EXEC USP_KILLPROCESS "MYDATABASENAME"

[fatherjack]

thanks for this, just a couple of points ->

>>Create this procedure in Master Database and execute this proc before restoring any database.

when you say create this procedure, do you mean actually create a stored procedure or just paste this code in the scheduled job so that it runs before the restore command ?

[MAK]

run the code first, that will create the procedure in the master database

in the Job write this as job step

use master
go
EXEC USP_KILLPROCESS "MYDATABASENAME"
go
restore database mydatabasename from disk ='blah' with replace blah blah

[fatherjack]

oops,

i ran the first SQL and got this error:

Server: Msg 207, Level 16, State 3, Procedure USP_KILLPROCESS, Line 25
Invalid column name 'KILL '.

Line 25 is the BEGIN statement !?

we are running SQL2000 sp3, would this matter?

[MAK]

run this statement first.
your quoted identifier is on.


use master
go
SET QUOTED_IDENTIFIER Off
go
create proc ......

[fatherjack]

many thanks.

all systems are scheduled to go at 21:00.

i will let you know tomorrow how it goes.

thanks again

[fatherjack]

all worked perfectly - thank you

[Rawhide]

Just a suggestion, but database names can be up to 128 characters. So, either make the variable varchar(128) or use the sysname datatype.

I prefer using the sysname datatype whenever the variable will hold the name of a database object of any sort.