Stop backup while in progress

JosephKing

Is there a way to stop the database backups while they are in progress.  From the GUI selecting stop on the job does not stop the actual work being done. It continues to happen and never changes it's status.

I know you can stop the service and it should quit but what does that do to any database that was in progress.

Is there a better way to do this.

petey2

Stopping the SQL Backup Agent service is the recommended way to stop active backups, though it will stop all active SQL Backup backups and restores.

If you really must, you can kill the backup job using the 'kill' command in SSMS.  Use sp_who2, look for the 'SQBCoreService.exe' process running the 'BACKUP DATABASE' command.  There should be a few of those, look for the one with the highest CPUTime and DiskIO values.  Kill the corresponding SPID.

SQL Backup should show something like this in the log:

SQL error 596: Cannot continue the execution because the session is in the kill state.

SQL error 3013: BACKUP DATABASE is terminating abnormally.

SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_E28DB706-C3A0-4A60-BA2A-0531B0B8932B:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).

SQL error 3202: Write on "SQLBACKUP_E28DB706-C3A0-4A60-BA2A-0531B0B8932B" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)

SQL error 3204: The backup or restore was aborted.

To be sure that the process was stopped 'cleanly', check the msdb..backupset table to ensure SQL Server did not log the backup as a valid backup.  If it did, that would be bad.  If that was a full backup, it becomes the base for any future differential backups (at least until the next full backup).  If it was a log backup, the log chain would be broken because you now have an incomplete backup file.

So only do this if you understand the possible consequences and how to address them.