asked on

How to Cancel a SQL Backup

Hello:

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
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

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!

TBSupport

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

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.

TBSupport

ASKER

Hello:

Thanks, for the excellent responses!

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

TBSupport

Anthony Perkins

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

TBSupport

ASKER

HI Anthony:

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

Also, where is #2?

Thanks!

TBSupport

Anthony Perkins

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"

TBSupport

ASKER

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?

TBSupport

Anthony Perkins

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

TBSupport

ASKER

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?

Thanks!

TBSupport

Anthony Perkins

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.

TBSupport

ASKER

Thanks, Anthony!

TBSupport