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.

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. Jul 7, 2017 at 15:02
  • yes killed thank you, how can i take it again now.. 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. 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
    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
    Jul 7, 2017 at 15:43

3 Answers 3

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.

0

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

sp_who2 'active'
kill {SESSION_ID}
1
  • killed , but how can i take back up of this 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.

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.