0

I have a big database (150 GB) that needs a back up and I don't have space in current machine, so I am pointing it to the different box which has space. After executing the cmd, it is executing the query but taking long time, not showing stats also.

Query used:

backup database [DBName] to 
disk = '\\Servername\H:\Backup7-7-2017\dbname.bak'
with copy_only, stats = 1

go

It is running for more than 5 minutes and no change on stats, and in destination folder the file is not created yet, so I am planning to go for manual backup from GUI. Now I want to kill this running backup as it is in suspended mode, when I checked in activity monitor.

Improve this question
7
  • Have you tried killing the query? ALT+Break in SSMS, right-click "kill process" from the activity monitor, or CTRL+C in sqlcmd. It probably won't end immediately but just give it a little time. Also I'd say give it more time in general, a backup of a large DB can take some time.
    – LowlyDBA - John M
    Commented Jul 7, 2017 at 15:02
  • yes killed thank you, how can i take it again now..
    – Harris Hilton
    Commented Jul 7, 2017 at 15:23
  • 2
    I suggest you read up on some of the basic documentation, however it is doubtful that the GUI is going to yield any performance improvements for the process. You'll need to ask a separate question if you have further concerns. Please mark whichever answer you used as correct to close out this question as well.
    – LowlyDBA - John M
    Commented Jul 7, 2017 at 15:25
  • 1
    I recommend not using the GUI for backing up a large database. You're likely to have the GUI become non-responsive while the backup runs. You can use the GUI to select backup options, then click the "Script" button at the top to script the BACKUP statement to a new window. Cancel out of the GUI, and run the script.
    – AMtwo
    Commented Jul 7, 2017 at 15:42
  • 2
    I suspect you'll need to use a backup path like \\Servername\H$\Backup7-7-2017\dbname.bak .... using a $, not : to access the administrative share. Also, try backing up a smaller database first to make sure your permissions to access and write to that share are sufficient.
    – AMtwo
    Commented Jul 7, 2017 at 15:43

3 Answers 3

Reset to default
2

There are many ways to kill the backup - exactly the same as you would any query.

  • ALT+Break in SSMS
  • Right-click "kill process" from the activity monitor
  • CTRL+C in sqlcmd
  • and so on...(these seemed most appropriate given the implied knowledge of the asker)

It probably won't end immediately but just give it a little time. Also I'd say give it more time in general, a backup of a large DB can take some time and may not start producing stat updates immediately. Check out previous backup times as a guide if possible.

Improve this answer
0

First, get a session ID with sp_who2, then end the session with kill

sp_who2 'active'
kill {SESSION_ID}
Improve this answer
1
  • killed , but how can i take back up of this
    – Harris Hilton
    Commented Jul 7, 2017 at 15:22
0

  1. First of all find the session from which the backup is running. To find that use Sp_who2 and check for the command column to find "Backup...'.

    Now just confirm that it is the same one that you have initiated by taking a look at the query fired for corresponding spid.Check this by :

    DBCC Inputbuffer(SPID); /Use the spid here/

    Once you are sure of the SPID just kill the session by using :

    KILL (SPID)

    And then wait till it disappears.

  2. There will be STOP button next to EXECUTE in SSMS. You can use it to cancel the query execution from ssms.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.