How to drop a SQL database stuck in Restoring...

No idea where I got this (it's been in Drafts forever), and now I kinda want to do it in PowerShell, but here's some T-SQL to drop a hella stuck database (if plain old restore database @dbname doesn't work).

 1DECLARE @dbname sysname
 2DECLARE @spid int
 3DECLARE @dbid int
 4SET @dbname = 'big'
 5
 6SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
 7SELECT @dbid = db_id(@dbname)
 8
 9WHILE db_id(@dbname) IS NOT NULL
10	BEGIN
11		WHILE @spid IS NOT NULL
12			BEGIN
13				EXECUTE ('KILL ' + @spid)
14				SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
15			END
16
17		SELECT @spid = min(spid) from master.dbo.sysprocesses where cmd = 'RESTORE DATABASE'
18		WHILE @spid IS NOT NULL
19			BEGIN
20				EXECUTE ('KILL ' + @spid)
21				SELECT @spid = min(spid) from master.dbo.sysprocesses where cmd = 'RESTORE DATABASE'
22			END
23
24		EXEC ('DROP DATABASE ' +  @dbname)
25		WAITFOR DELAY '00:00:05'
26	END
27GO