Can I stop a SQL database backup after it has already started?
Straight to answer your questions :
Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much?
You cannot pause a backup - either running using Tsql or using sql agent job. If your db is in mirroring, then when you failover the backup gets killed.
Since you are on sql server 2008, recently I ran into a weird situation that caused an outage of one of our critical app is that if you are not on the latest SP (2008 SP4) - we were running sp1 on on node that had a bug and was fixed in SP1+CU4 - It was resolved in CU4 for SP1:https://support.microsoft.com/en-US/help/973602. Search, “970133” for: FIX: When you create a compressed backup for a database in SQL Server 2008, you cannot stop the backup operation if the backup operation stops responding.
You can stop it or kill it using the kill spid
command.
you have to be cautious as if you kill a job that is already in progress, it has to ROLLBACK
which will take some or more time.
Is a backup just a job, that I would just stop the job?
Depends on how you are running the backup. If you run it using SQL Agent job, then its a job which you can STOP
or if you are running it directly from SSMS or SQLCMD, then you can CANCEL
or KILL
the command.
When doing a backup, are you able to view the progress, and see an estimated completion time of the backup? If so, how do you view its progress?
You can use DMVs to monitor the backup (or restore) progress (if you are not using WITH STATS = number
):
----- find out the ETA time for restore and backup progress
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
Some food for thoughts :
- Start checking what is causing "slow performance" - analyze Wait stats, locking and blocking problems, run sp_whoisactive, etc
- If you really found out that doing backups is causing the slowness then schedule backups when there is minimal activity and follow Aaron's advise and make sure to read some common backup myths - from Paul Randal.
- There are Options to Improve SQL Server Backup Performance as well.
I will be assuming that you are using native backups as you have not mentioned a specific product. However, depending on what backup product you are using (Litespeed, RedGate Hyperbac, etc) you may be able to stop a backup but it may not be as simple as clicking stop from within management studio for an Ad-Hoc backup command. For a native backup the engine should respond quickly to a stop command.
With this in mind the bigger question from the community will be "Why would you want to stop a backup in progress?". Unless you have pretty solid reasons for doing this, and I would say that poor performance isn't the best reason (to me that's an argument for better scheduling of maintenance operations or a better recovery plan) and you really shouldn't do it. Backups are your means to recovering your server and minimizing the risk to your data should things go south, and if you ask any DBA things will eventually go south if a server is around long enough. Ask us, we're not paranoid, they really are out to get us!
All kidding aside, as far as your second point to the first question you can have backups run ad-hoc or by using SQL Server Agent. If you haven't dealt much with coding a backup routine I would advise against maintenance plans not because they are bad but because they are clunky. I would use Ola Hollengren's maintenance suite which provides you just about everything you need out of the box and all you need to do is build your schedules. You can find this at http://ola.hallengren.com and read the documentation there it is really good.
Your second question opens the door to some very interesting areas of the engine. Yes you are able to view the progress of a backup. You can do this by providing a value to the WITH STATS parameter on an Ad-Hoc command or by viewing sys.dm_exec_requests and matching the SPID for the backup to the session in the requests, you can use the COMMAND column to assist you in narrowing your search or sp_who2. You will see percent_complete as a returned column in this DMV which is populated for certain commands of which BACKUP is one. You can utilize this and the estimated_completion_time which is the time remaining in milliseconds to get a rough estimate of when the command will complete. For some commands this is more accurate than for others (DBCC SHRINKFILE is notoriously bad for underestimating). You can also find scripts online that will assist you with the calculations which can get a bit complicated depending on what you are trying to do.