I am looking to do a full database backup of a SQL database in SQL Server 2008.
My questions are:
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 :
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.
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
BEGIN
-- 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')
AND [command] LIKE 'BACKUP DATABASE%'
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)
DECLARE crsr CURSOR FOR
----------------------------------
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
WHILE @@FETCH_STATUS = 0
BEGIN
----------------------------------
EXEC sp_executesql @sqlcommand
----------------------------------
FETCH NEXT FROM crsr INTO @sqlcommand
END
CLOSE crsr
DEALLOCATE crsr
-- If success send mail
IF @@ERROR = 0
BEGIN
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'
END
END
