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!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Lesson:  Don't use the GUI.  Script it out (better still get hold of Ola Hallengren's Backup scripts).  Then you don't have to do any juggling.

And no, you did not damage any database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
TBSupportAuthor Commented:

Thanks, for the excellent  responses!

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

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Anthony PerkinsCommented:
>>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.
TBSupportAuthor Commented:
HI Anthony:

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

Also, where is #2?


Anthony PerkinsCommented:
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"
TBSupportAuthor Commented:
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?

Anthony PerkinsCommented:
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
TBSupportAuthor Commented:
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?


Anthony PerkinsCommented:
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.
TBSupportAuthor Commented:
Thanks, Anthony!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.