Link to home
Start Free TrialLog in
Avatar of TBSupport

asked on

How to Cancel a SQL Backup


Let's say that, in SQL Server 2012, you start a manual backup of a database.  But, during the backup, you realize that the database is 95GB and the drive that you're backing up to only has approximately 20.0 GB of space.  And, the drive contains some of the production SQL database files!

So, I needed to cancel this manual backup, as quick as possible.

I found the following script online that led me to session 101:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

So, I ran "kill 101".

But, at least at first, it did not work.  I mean, I think that it stopped the backup, eventually, but the backup began again at 10%.  (It was at 60%, originally.)

So, I ran that script again and it led me to sessions 101, 103, and 104.  So, I simply ran the same "kill" statement again against each of those session IDs.  It worked!  The backup stopped, and I received an error message saying that the backup could not run on the server.

Good.  The drive did not fill up with extra space, because the backup file that I was making disappeared, the database did not go into "Suspect" mode, and the manual backup stopped.  I was able to proceed with my work in the application that integrates with that database.

So, I have two questions.  Is the scripting that I ran "good enough" for killing a manual database backup?

Secondly, will this scripting and kill statement cause damage to the database?

Thank you!

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
When you kill a session the SQL Server engine will rollback all commands that was ran so it can go back to the previous state, so that's why it took long on your case.
Avatar of TBSupport



Thanks, for the excellent  responses!

I don't know how to script it out.  So, for my future reference, can I use this remedy?

>>I don't know how to script it out.  <<
Actually you do, you are just not aware of what button to press.  Here are the steps:
1. Do exactly what you did last time, just don't press OK.
2. Click on the Script icon on the top left corner of the dialog box.
3. The default is Script Action to New Query Window

You now have the equivalent script.
HI Anthony:

In step #1, you say "just don't press OK".  Where was this?

Also, where is #2?


Let me try it  this way:
1.  Start SQL Server Management Studio (SSMS)
2,  In the Object Explorer expand the Databases object
3,  Right-click on  the database you want to backup
4.  Select "Tasks"
5.  Select "Back Up..."
6. Do exactly what you did last time, just don't press the OK button.
7. Click on the "Script" icon on the top left corner of the dialog box.
8. The default is "Script Action to New Query Window"
Thanks, Anthony!  I did not know about that scripting!  That's awesome--and, so are you!

Now, how do I use this scripting to cancel the backup?  Are you telling me that this scripting will do so?

It is just simpler to kill a running query than stopping the GUI.  All you have to do is click on the red dot or just press Alt+F5
Hi Anthony:

Quick question:  Despite the fact that I used the GUI, instead of scripting, was the script and remedy that I found and used correct?


was the script and remedy that I found and used correct?
Yes. but I don't quite understand why you had to kill more than one connection.  But then I very rarely use the GUI in the first place.
Thanks, Anthony!