How to cancel running backup extended stored procedure?

phaluska · December 16, 2005 5:34PM

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 · December 19, 2005 6:00AM

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 &#40;@dbName varchar&#40;128&#41;&#41;
as
     DECLARE @ProcessId varchar&#40;4&#41;

     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 &lt;&gt; -1
BEGIN
     --print 'Kill ' + @processid
     Exec &#40;'KILL ' +  @ProcessId&#41;
     --Kill @ProcessId
     FETCH NEXT FROM CurrentProcesses INTO @ProcessId
END
CLOSE CurrentProcesses
DeAllocate CurrentProcesses

How to cancel running backup extended stored procedure?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk