SQL Express Backup and Email Notification

Hi There!

We have a number of sites which use SQL Express 2005/2008.

I want to setup a scheduled backup, with email notification for this twice a business working day, automatically with some form of email notification.

Also I would like know how I can setup email notification for successful or failed backups on my full sql 2008. I have the system setup, but I can't see where to configure the destination address on successful or failed backups.

Thanks in advance.
networknAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should help for Express:

http://www.sqldbatips.com/showarticle.asp?ID=27
0
 
onlyamir007Commented:
http://www.teratrax.com/tjs/

- Automate SQL Server & SQL          Server Express tasks with Teratrax Job Agent
- Receive e-mail notification of job status every time a job is          executed
- Schedule SQL Server backups and manage history of backup          files
- Schedule SQL batches and stored procedures to run locally          or remotely

0
 
networknAuthor Commented:
onlymir007,

I was hoping for a free product.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Have you tested out the one I provided which is free but it doesn't come with email notification.
If you want email notification, then you have to go for a paid product only..
0
 
networknAuthor Commented:
rrjegan17: That solution appears to work ok, pity about the email notification. Do you have a recommendation on something inexpensive?

What about the full SQL Question?
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then you can try any one of the approaches mentioned below to send email:

http://support.microsoft.com/kb/312839
http://www.mssqltips.com/tip.asp?tip=1625

Although there are some unofficial methods floating around, I wouldn't recommend those.
0
 
networknAuthor Commented:
Thanks, we are in the process of moving offices, the next week and a bit will be pretty hectic, I'll review these when I have a moment.
0
 
monicaiCommented:
build a batch file and let it run on Windows scheduled jobs:

the following script will have the following script below.

the OSQL part is calling the sql command to run the backup script using the command prompt.  you can change -U and -P to -E if your sql server allows Windows Authentication and can use trusted connection.  the results of the backup is printed on a file called backupresults.txt.

the second line of the script is emailing the contents of backupresults.txt to a particular email address.  You can use an any SMTP address available inside the organization.  SMTP comes as an additional package in an IIS server.   To setup SMTP, you can refer to this website http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/e4cf06f5-9a36-474b-ba78-3f287a2b88f2.mspx?mfr=true 

Also, you need to download blat.exe to run the email portion of this script.  Blat is a free windows command utility that sends emails.  You can download a copy here:  http://www.blat.net/

the last line of the script is deleting the backupresults.txt.  If you don't delete it, OSQL will continue appending the results on the backupresults.txt file and send the contents to the recipients  email address.  

Feel free to modify the code below according to your requirements.  




OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE [mydatabase] TO DISK = 'c:\mydatabase.dat_bak'"  >> backupresults.txt

blat.exe results%datenow%.txt -to youremail@contoso.com  -server yoursmtpserver.contoso.com -port 25 -f senderemail@contosocom -subject "any subject you want"

del backupresults.txt

Open in new window

0
 
monicaiCommented:
Also, if you are going to use a full SQL 2008 soon, you can use Database Mail to configure it.  Database mail will require the same SMTP address that I have mentioned earlier on my post.  Once the database mail is configured, you can modify your operators inside the SQL Agent service to configure your destination email addresses and use this on your SQL Agent jobs notifications where you can invoke your backup schedule tasks in SQL Server.

For more information you can see step-by-step instructions in this post:

Setup database mail in SQL Server  2008
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
Setup Operators :
http://technet.microsoft.com/en-us/library/ms175962.aspx
http://www.ideaexcursion.com/2009/01/05/configuring-sql-server-agent-operators/
SQL Agent jobs:
http://msdn.microsoft.com/en-us/library/ms187880.aspx

0
 
networknAuthor Commented:
Hi There!

Sorry for the lack of response. We have been slammed at work, though I have the perfect reason to need to do a restore of the sqlmaint.exe method of backup tomorrow, so once I have done that I will close the question and award points.
0
 
networknAuthor Commented:
rrjegan17: this works a treat, the problem is I would also like an email notification to occur based on the status of the job, or at least email daily, the log file?
0
 
networknAuthor Commented:
http://pastebin.com/H0VPpqeu

I have renamed a few of the details, but they remain consistent.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Found out this useful article which might help you out..

http://www.mssqltips.com/tip.asp?tip=1795

Just send mail using msdb.dbo.spSendMail at the end of backup job to get mails delivered after successful execution of your job..
0
 
networknAuthor Commented:
Sorry for the delay. There are multiple ways to d what I wanted to do but I have settled on a hybrid solution for what i want to do between sqlmaint and  the last solution posted. Thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.