How to cancel running backup extended stored procedure?

phaluskaphaluska Posts: 4
edited December 19, 2005 6:00AM in SQL Backup Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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
    
Sign In or Register to comment.