How to cancel running backup extended stored procedure?

phaluska

I have written C# application to use Redgate's generated backup/restore extended stored procedures queries to run backup/restore SQL backups.

I would like to ask if there is a way, or it will be in the future, with using your 'sqlbackup -sql "..."' extended stored procedure to cancel running backup/restore operation without restarting SQL service. Thanks for any suggestions.

Brian Donahue

Hello,

I don't know if there is a better way to do this, but you could use SQL Server queries to kill the process. You could write a stored procedure that selects from sp_who2:

CREATE PROC killConnections (@dbName varchar(128))
as
     DECLARE @ProcessId varchar(4)

     DECLARE CurrentProcesses SCROLL CURSOR FOR
     select spid from sysprocesses where program_name='SQL Backup' order by spid 
     FOR READ ONLY
OPEN CurrentProcesses
FETCH NEXT FROM CurrentProcesses INTO @ProcessId
WHILE @@FETCH_STATUS <> -1
BEGIN
     --print 'Kill ' + @processid
     Exec ('KILL ' +  @ProcessId)
     --Kill @ProcessId
     FETCH NEXT FROM CurrentProcesses INTO @ProcessId
END
CLOSE CurrentProcesses
DeAllocate CurrentProcesses