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