I am looking to do a full database backup of a SQL database in SQL Server 2008.

My questions are:

  • Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much? Is a backup just a job, that I would just stop the job?
  • 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?
  • Why do you think the backup is "slowing things down"? – Aaron Bertrand Oct 21 '14 at 13:16
  • I think my concern was of system resources, if I had noticed any major performance issues, but overall, my main question is how to stop a backup in progress if necessary. – kyle_13 Oct 21 '14 at 13:21

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

Some food for thoughts :

  • 1
    Hi @Kin, thanks very much for the succinct answers. This is all really helpful info. I was simply using the 'wizard' to backup the db, by right-clicking the db and backup, and it did show progress and a stop option, which I fortunately did not have to use because it ran rather quickly, but the rollback is definitely a consideration. – kyle_13 Oct 21 '14 at 19:02

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.

  • Hi @Travis Page, thanks very much for the in-depth explanation. I was using native backups, just using the right-click on db, and backup. Your insight is really helpful and appreciated. – kyle_13 Oct 21 '14 at 19:00
  • Glad I could help you out. If you are scheduling backups in this manner I would suspect that you may have some databases in recovery models that could bite you at a later date. If in doubt you can read up on the recovery models here. If you are in full recovery you will need to be scheduling log backups as well as full backups of the DB otherwise you are going to end up filling your disk with a log file at some point. Best of luck! – Travis Page Oct 23 '14 at 13:50
DECLARE @BackupCount BIGINT, @GetSPid BIGINT = NULL, @KillCommand NVARCHAR(1000)=N'', @mailfrom NVARCHAR(100)=N'', @subject NVARCHAR(100)=N'', @mailbody NVARCHAR(MAX)=N'', @JobName NVARCHAR(100)=N'', @tableHTML NVARCHAR(MAX)=N'', @tableHTML1 NVARCHAR(MAX)=N'', @Table1 NVARCHAR(MAX)=N''
--SELECT @jobName = name FROM msdb..sysjobs WHERE job_id = $(ESCAPE_NONE(JOBID))

SELECT @BackupCount = COUNT(*)
    FROM [master].[dbo].[sysprocesses] sp
        INNER JOIN [master].[dbo].[sysdatabases] sd ON sp.[dbid] = sd.[dbid]
        INNER JOIN [master].[sys].[dm_exec_connections] dc ON sp.[spid] = dc.[session_id]
    WHERE sd.[name] NOT IN ('master', 'model', 'msdb')
        AND sp.[cmd] LIKE 'BACKUP DATABASE%'

IF (SELECT @BackupCount) > 0

-- Data collect

    SELECT @Table1 = @Table1 + '<tr style="font-size:13px;background-color:#FFFFFF">' + 
        '<td>' + CAST(@@SERVERNAME AS NVARCHAR(50)) + '</td>' +
        '<td>' + CAST(DB_NAME(ERQ.[database_id]) AS NVARCHAR(50)) + '</td>' +
        '<td>' + CAST(LEFT(EST.[text], CHARINDEX(' with', EST.[text], 1)-1) AS NVARCHAR(300)) + '</td>' +
        '<td>' + CAST(ERQ.[percent_complete] AS NVARCHAR(10)) + '</td>' +
        '<td>' + CAST(ERQ.[total_elapsed_time]/1000/60 AS NVARCHAR(10)) + '</td>' +
        '<td>' + CAST(ERQ.[estimated_completion_time]/1000/60 AS NVARCHAR(10)) + '</td>' + 
        '<td>' + CAST(ERQ.[wait_time]/1000/60 AS NVARCHAR(10)) + '</td>' + 
        '<td>' + CAST(ERQ.[last_wait_type] AS NVARCHAR(100)) + '</td>' +
        '<td>' + CAST(FORMAT(ERQ.[start_time], 'dd/MM/yyyy HH:mm:ss') AS NVARCHAR(30)) + '</td>' + 
        '<td>' + CAST(FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss') AS NVARCHAR(30)) + '</td>' + '</tr>'  
    FROM sys.dm_exec_requests AS ERQ
        CROSS APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
    WHERE CAST(DB_NAME(ERQ.[database_id]) AS NVARCHAR(50)) NOT IN ('master', 'model', 'msdb')

    SET @tableHTML1 = 
        N'<table border="1" align="Left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
        N'<tr style ="font-size:13px;font-weight: normal;background: #FFFFFF"> 
        <th align=left>InstanceName</th>
        <th align=left>DatabaseName</th>
        <th align=left>Command</th>
        <th align=left>Percent</th> 
        <th align=left>ElapsedTime(min)</th>
        <th align=left>CompletionTime(min)</th> 
        <th align=left>WaitTime(min)</th>
        <th align=left>LastWaitType</th>
        <th align=left>StartTime</th> 
        <th align=left>CollectTime</th> </tr>' + @Table1 + '</table>'

    SET @tableHTML = @tableHTML1

-- Get SPID then kill backup processes with cursor
    DECLARE @sqlcommand NVARCHAR(500)
    SELECT 'kill ' + CAST(sp.[spid] AS VARCHAR(5))
        FROM [master].[dbo].[sysprocesses] sp
            JOIN [master].[dbo].[sysdatabases] sd ON sp.[dbid] = sd.[dbid]
            JOIN [master].[sys].[dm_exec_connections] dc ON sp.[spid] = dc.[session_id]
        WHERE sd.[name] NOT IN ('master', 'model', 'msdb')
            AND sp.[cmd] LIKE 'BACKUP DATABASE%'
    OPEN crsr
    FETCH NEXT FROM crsr INTO @sqlcommand
        EXEC sp_executesql @sqlcommand
    FETCH NEXT FROM crsr INTO @sqlcommand
    CLOSE crsr

-- If success send mail
        IF @@ERROR = 0
            SELECT @mailfrom = 'SQL Admin <' + CAST(SERVERPROPERTY('ComputerNamePhysicalNETBIOS') AS VARCHAR(50)) + '@domain.com>'
            SELECT @subject = N'SQL Server Backup Process is Killed! [Job: ' + @JobName + ']'
            SELECT @mailbody = N' ' + @@SERVERNAME + ' Instance Backup Process is killed due to out of work hours: 18:00-02:00. Informations for backup processes like below.
            <br><br>' + @tableHTML

            EXEC msdb.dbo.sp_send_dbmail
            @profile_name= 'sqladmindaemon',
            @recipients= '<backupteam@domain.com>',
            @copy_recipients= '<sqlteam@domain.com>',
            @from_address = @mailfrom,
            @reply_to = '<sqlteam@domain.com>',
            @subject = @subject,
            @body = @mailbody,
            @body_format = 'HTML'
            --,@importance = 'HIGH'
  • thanks for posting this script, I have not tested it, but seems relevant to the question. I can see it still uses sysprocesses but it should work. It would be great if you added some information about the script, alongside it in the answer, and how long you have been using it, and how it made your life easier. welcome to our dba forum. – Marcello Miorelli May 18 '20 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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