This is the stored procedure for terminating the process.
Use master
Go
---------------------------------------
-- Disable all access processes to the specified database
---------------------------------------------
Create proc killspid
@ Dbname varchar (200) -- Name of the database for which the process is to be disabled
As
Declare @ SQL nvarchar (500)
Declare @ spid nvarchar (20)
Declare # TB cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ dbname)
Open # TB
Fetch next from # TB into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # TB into @ spid
End
Close # TB
Deallocate # TB
Go
What your script means:
Stored Procedure in the master database: killspid,
If your goal is the same, execute the restored SQL statement as follows:
Use masrer
Go
Exec killspid 'dbname' -- change dbname to the name of the database to be restored.
Go
-- Write a restore statement here to restore your database
Go
If your intention is not the above, but to create a killspid stored procedure on the data to be restored, it is estimated that you will not achieve your goal, because when you want to execute killspid, the process executing killspid is also in the list of processes you want to kill. What you said is that kill cannot be used to terminate the process;
We recommend that you use SQL statements instead of stored procedures:
Use master -- use master must be used
Go
Declare @ dbname nvarchar (500)
Set @ dbname = 'dbname' -- change dbname to the name of the database to be restored.
Declare @ spid nvarchar (20)
Declare # TB cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ dbname)
Open # TB
Fetch next from # TB into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # TB into @ spid
End
Close # TB
Deallocate # TB
Go