Verifying SQL server backups - how do I tell if they failed
-
Hi,
I want to write a script that will query 60+ sql server instances and determine when the last backup was done and if the backup was successful or not.
The backupset table doesn't appear to have a 'status' column. Are there any assumptions that I can make about other columns? For example, is the backup_finish_date null if an error occured? Ditto for backup size?
I'm a newbie to SQL server (20+ years of Oracle). What is a typical/normal alerting mechanism if a SQL server backup fails?
We are between SQL server DBAs at this point and I'm being asked to pick up the slack.
-
Hi Evelyn,
Assuming your backups are run by scheduled jobs, the msdb.sysjobhistory table stores the information you want in it's status and run_date columns. msdb.sp_help_jobhistory queries the table. See 'sysjobhistory' and 'sp_help_jobhistory in SQL Server Books OnLine.
If you want to be notified of a failed backup job, set up email notification in each job. To do that in Enterprise Manager, expand the 'Management' folder for the SQL Server instance, expand 'SQL Server Agent', expand 'Jobs', double-click the job and select the 'Notifications' tab.
Greg
Greg
-
You can also parse through the SQL Server error logs to check for failed backups. If a backup or restore operation failed, an entry will get written into the sql error logs.
-
can a script be written to read through each server's SQL Logs, then return which
servers backups experienced a failure?
i'm looking for the same type of solution without adding an additional step in each and every job.
any ideas would be greatly appreciated!
thanks in advance
_________________________
-
Generally you should have some sort of email notification setup, whether it's using SQL Mail and Outlook, CDO or something like XPSMTP. Then when a job thats running a backup fails you should get an alert of some description from the failure of the job.
More completely, the backup is not guaranteed to have been successful unless you've performed a successful restore. We all do test our backups on a regular basis don't we
You can setup log shipping to a basic workstation and continually restore all your backup files, that way you're continually testing the backups.--------------------
Colt 45 - the original point and click interface
-
I'm new to SQL Server as well coming from an Oracle environment. Due to the way our servers are set up I am unable to set up notification thorugh Enterprise Manager, so I set up the following .vbs script to e-mail me when there is an error in the maintenance plan logs (since our backups run through a maintenance plan). You can put this script in a scheduled task to run as you need. I have also set up a simialr script to check the error logs. As I said I am pretty new to windows and SQL SQL Server so this may not be the best way, but it works for me.
DirectoryPath = "Path on server where maintenance log is located"
dtmDate = Date
strDay = Day(dtmDate)
If Len(strDay) < 2 Then
strDay = "0" & strDay
End If
strMonth = Month(dtmDate)
If Len(strMonth) < 2 Then
strMonth = "0" & strMonth
End If
strYear = Year(dtmDate)
strTargetDate = strYear & strMonth & strDay
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set FileList = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_Directory.Name='" & DirectoryPath & "'} Where " & "ResultClass = CIM_DataFile")
For Each objFile In FileList
If InStr(objFile.FileName, strTargetDate) Then
strFN=DirectoryPath & "\" & objFile.Filename
Wscript.Echo strFN
Set objMPFSO = CreateObject("Scripting.FileSystemObject")
Set objMPFile = objMPFSO.OpenTextFile(StrFN & ".txt")
Do Until objMPFile.AtEndOfStream
strText = objMPFile.ReadLine
If InStr(strText, " Error ") Then
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "senders@email"
objEmail.To = "receivers@email"
objEmail.Subject = "Maintenance Log ERRORS on ServerName"
objEmail.Textbody = "An Error was detected in file " & strFN
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"exchange.server.name"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Exit Do
End If
Loop
objMPFile.Close
End If
Next
-
I've always set scripts that check the actual backup files on the disk. That way I can be sure I have a backup file, get it's size (useful for growth tracking) and a failure lets me know which database.
-
I would create a job that runs a CMD file after your last backup should have completed that would pipe the log through Find looking for errors. Email the result file back to yourself, and you've got an easy check in the morning.
I use this technique to inspect my DBCC results looking for errors.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Since there are so many instances, you don't have a single point that you can query to get that info unless all the servers are linked, and I think that might get kinda ugly. I think that your first and best bet is to have the backup jobs notify you if they fail, assuming all instances have email (one of my servers does not, PITB). I would also have the notify log the job when it completes, not when it succeeds or fails, so you can write a script that would parse the logs and look at it that way.
If you don't have email on the instances, you might look at BLAT on Sourceforge for a send-only email program.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply