Post reply

Monitoring Nightly Database Backups

  • January 19, 2010 at 9:33 am

    #140718

    Hi everyone,

    I am a new SQL Server DBA, so I don't know much about it just yet. I have SQL 2000 and 2005 databases that are hosted on multiple servers. I would like to be able to monitor the nightly backups of these databases and have an alert sent if any of them should fail. I don't know how to go about achieving this.

    Can anyone provide some helpful suggestions such as available scripts or GUI tools that aid in this task. Also, are there any tools that will provide the DBA with an enterprise view of all databases to aid in monitoring statistics, performance and etc.

    I appreciate all suggestions.

  • January 19, 2010 at 9:42 am

    #1105524

    You can set up alerts for any failed jobs and get notified by email.

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • January 19, 2010 at 9:52 am

    #1105541

    Software is available from multiple vendors on external monitoring and scheduling task within SQL Server. Quest is one that is widely known in the industry for this but others may suite you better, just depends on your specific needs and how much money your company is able to spend.

    You can setup emails as The_SQL_DBA noted within each SQL job you have scheduled. This would require you configure the SQL Mail (SQL 2000) and Database Mail (SQL 2005). The SQL 2000 version of email notification can be a headache to setup, depending on how much time you have to do it. BOL (Books Online) has information on how to setup both versions.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • January 19, 2010 at 9:57 am

    #1105542

    As a new SQL Server DBA, make sure to download and install SQL Server Books Online. I opened my version and searched for:

    'create job'

    'create maintenance plan'

    You can use BOL to research how to add email and alerts to jobs, also.

    The GUI you're asking for is SQL Server Management Studio (SSMS), it's the interface to your database server and various databases.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • January 19, 2010 at 11:02 am

    #1105587

    Do you have SCOM?



    My blog

  • January 19, 2010 at 11:04 am

    #1105591

    If you don't have SCOM this may also be helpful http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/ and of couse Books OnLine.



    My blog

  • January 19, 2010 at 11:29 am

    #1105609

    You could find out if SMTP server is available from your server admin. Then, you could easily configure email notifications using database mail feature. Configure a database mail profile and test email notificaiton.

    After this, you could enable that profile on the alert system (right click SQL server Agent and properties).

    Then, you could add a step to your job to send notification if the backup fails.

    Eg.

    sp_send_dbmail @profile_name = 'Sample'

    , @recipients = 'DBAteam@abc.com'

    , @copy_recipients = 'myself@abc.com'

    , @subject = 'Nightly backup job has failed'

    , @body = 'Nightly backup job has failed, please check immediately'

    , @importance = 'HIGH'

    It is very simple to configure notifications in SQL server 2005 because of database mail.

    M&M

  • January 19, 2010 at 12:55 pm

    #1105694

    I appreciate all the responses.

  • January 19, 2010 at 12:59 pm

    #1105700

    'Accept input parameters

    Dim jobType

    'first parameter

    jobType= Wscript.Arguments.Item(0)

    Set objMessage = CreateObject("CDO.Message")

    objMessage.Subject = "Message Alert from SQL Server: " & jobType & " job failed"

    objMessage.From = "DBA.org"

    objMessage.To = "OtherDBA.org"

    objMessage.TextBody = "The " & databaseName & " " & jobType & " job running in the SQL Server instance failed on " & Now() & vbCrLF & vbCrLf & "Please look at this problem ASAP " & vbCrlf & vbCrlf & vbCrlf & vbCrlf & "- The SQL Server Administrator/DBA -"

    'This section provides the configuration information for the remote SMTP

    'server.Normally you will only change the server name or IP.

    objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    'Name or IP of Remote SMTP Server

    objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.domain.local"

    'Server port number(typically 25)

    objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    objMessage.Configuration.Fields.Update

    objMessage.Send

    Set objMessage = Nothing

    When I attempt to execute this code, I get an error:

    Subscript out of range

    800A0009

    Microsoft VBScript runtime error

    If someone can point me in the right direction, that will be great.

  • January 19, 2010 at 9:24 pm

    #1105813

    You're better off using Database Mail with the code Mohammed suggested above

    Easier to configure than trying to debug your VBscript...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply