How can we help you today? How can we help you today?

How to cancel running backup extended stored procedure?

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.
phaluska
0

Comments

1 comment

Sort by
  • 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
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.