Announcement

Collapse
No announcement yet.

E-mail notification for offline backup Script

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • E-mail notification for offline backup Script

    Hi Folks,

    I am using a script to do an offline backup of an SQL database as follows:

    Code:
    "D:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\Binn\dbAttach.exe" /DATABASE="databasename" /backup /auto /user="user" /PASSWORD="password" /SERVER="server" /PATH="D:\folder\Wednesday.BAK" 
    if ERRORLEVEL 1 GOTO :ERROR
    echo SIMS Backup Suceeded > SuccessWednesday.txt
    
    
    GOTO :COMPLETE
    
    :ERROR
    echo Sims Backup Failed > failed.txt
    GOTO :END
    
    :COMPLETE
    echo Full Backup Suceeded >> SuccessWednesday.txt
    
    :END
    Now, Is there any way I could change the script so everytime it fails or completes succesfully it sends me an e-mail rather than writing to the txt file and maybe with different subject lines ???
    Last edited by L4ndy; 7th May 2009, 13:53.
    Caesar's cipher - 3

    ZKHQ BRX HYHQWXDOOB GHFLSKHU WKLV BRX ZLOO UHDOLVH LW ZDV D ZDVWH RI WLPH!

    SFX JNRS FC U6 MNGR

  • #2
    Re: E-mail notification for offline backup Script

    You can try one of the the command line tools MAPISend.exe or 'BLAT.exe for Windows' for your batch.

    \Rems

    This posting is provided "AS IS" with no warranties, and confers no rights.

    __________________

    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts

    Comment


    • #3
      Re: E-mail notification for offline backup Script

      Thanks a lot for the pointers Rems,
      I am a bit lost now about which one to use and I've since your post found out about Bmail.exe Send mail etc. Spoiled!
      Anyhow, If I may pick your brains again, in my original script would you suggest creating a batch file with the mailer exe and calling this batch fom my script or running it straight from the script? (Pardon me if this is a fundamental scripting question)
      Caesar's cipher - 3

      ZKHQ BRX HYHQWXDOOB GHFLSKHU WKLV BRX ZLOO UHDOLVH LW ZDV D ZDVWH RI WLPH!

      SFX JNRS FC U6 MNGR

      Comment


      • #4
        Re: E-mail notification for offline backup Script

        Why not use SQL Server to do the backup, use Database Mail to send the notification, and set up an Operator to receive the notification?

        Comment


        • #5
          Re: E-mail notification for offline backup Script

          Originally posted by joeqwerty View Post
          Why not use SQL Server to do the backup, use Database Mail to send the notification, and set up an Operator to receive the notification?
          Hi Joe,

          That would've been my preferred weapon of choice too, but we are using SQL 2005 express and we schedule the backups. AFAIK the express version does not have the database mail function and it does not support scheduling of backups (not out of the box anyway).
          The scripting method I use does work fine I just wanted to add the mail notification which any of the command line mailers mentioned looks like is fit for the job.
          Caesar's cipher - 3

          ZKHQ BRX HYHQWXDOOB GHFLSKHU WKLV BRX ZLOO UHDOLVH LW ZDV D ZDVWH RI WLPH!

          SFX JNRS FC U6 MNGR

          Comment


          • #6
            Re: E-mail notification for offline backup Script

            Gotcha. Carry on.

            Comment


            • #7
              Re: E-mail notification for offline backup Script

              Originally posted by L4ndy View Post
              The scripting method I use does work fine I just wanted to add the mail notification which any of the command line mailers mentioned looks like is fit for the job.
              You could use VBScript too. CDONTS / CDOSYS can be used to send mail, and the stuff is available in Server 2k & 2k3. With Powershell, use System.Net.Mail.

              -vP

              Comment


              • #8
                Re: E-mail notification for offline backup Script

                a bit fiddly to setup but I use mpack.exe and bmail.exe for this. I used to use another exe but it wasn't all that reliable .... will try and remember what it was.

                In my situation, I had to synchronise a load of data on a regular basis so wrote a batch file using robocopy which then e-mailed me the log files. It did a few nice things like putting the log files in dated folders and generating event log entries as well but the basic version should be all you need.

                here's an edit version of the longer one so you can see how I used it. I'm sure you'll be able to cut the bits out you need / want
                Code:
                @echo off
                REM Creating Log Folder
                md c:\#Scripts\Logs\Working\
                
                REM Event log at start of job run
                %windir%\system32\eventcreate.exe /T INFORMATION /ID 666 /D "DR Data replicaiton started"
                
                REM Replicating "Server1" into Data
                Robocopy e:\Server1 \\DR_server\Data /MIR /LOG+:c:\#Scripts\Logs\Working\Working.txt /R:3 /rh:1800-0400 /pf
                
                REM Event log at end of job run
                %windir%\system32\eventcreate.exe /T INFORMATION /ID 666 /D "DR Data replication finished"
                
                REM Move and rename log
                move c:\#Scripts\Logs\Working\Working.txt c:\#Scripts\Logs\%date:~-4,4%\%date:~-7,2%\%date:~-4,4%%date:~-7,2%%date:~0,2%.txt
                
                REM Event log e-mailing logs
                %windir%\system32\eventcreate.exe /T INFORMATION /ID 666 /D "DR Data replication logs being e-mailed"
                
                REM E-mailing Log file
                echo DR data replication from Server1 DR_Server logs attached. > body.txt
                c:\#scripts\mpack.exe -s "DR Replication" -d body.txt -c application/exe -o body.msg c:\#Scripts\Logs\%date:~-4,4%\%date:~-7,2%\%date:~-4,4%%date:~-7,2%%date:~0,2%.txt
                c:\#Scripts\bmail.exe -s 192.168.42.50 -f [email protected] -t [email protected],[email protected] -h -m body.msg
                
                REM Clean up
                del c:\#Scripts\body.msg
                del c:\#Scripts\body.txt
                rd c:\#Scripts\Logs\Working
                
                Exit
                hope that helps.
                This message represents the official view of the voices in my head

                Comment


                • #9
                  Re: E-mail notification for offline backup Script

                  @vonPryz

                  Thanks but I'll stick to the existing batch scripting I've got since I've got great limitations when it comes to batch - vbs conversions

                  @Graycat

                  Nice one,
                  I'm trying those mailers out but it looks like am going to use Bmail as well. I'll give mpack a go as well.
                  I'll definitely pass on that event ID 666 though.
                  The MOM administrator is a superstitious born again w****er

                  Cheers
                  Caesar's cipher - 3

                  ZKHQ BRX HYHQWXDOOB GHFLSKHU WKLV BRX ZLOO UHDOLVH LW ZDV D ZDVWH RI WLPH!

                  SFX JNRS FC U6 MNGR

                  Comment


                  • #10
                    Re: E-mail notification for offline backup Script

                    Originally posted by L4ndy View Post
                    I'll definitely pass on that event ID 666 though.
                    The MOM administrator is a superstitious born again w****er
                    do it! you know you want to
                    This message represents the official view of the voices in my head

                    Comment

                    Working...
                    X