?
Solved

SQL Express Backup and Email Notification

Posted on 2010-08-29
14
Medium Priority
?
1,217 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:networkn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
14 Comments
 
LVL 4

Expert Comment

by:onlyamir007
ID: 33552214
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 33552225
This should help for Express:

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

Author Comment

by:networkn
ID: 33552239
onlymir007,

I was hoping for a free product.
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33552245
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
 

Author Comment

by:networkn
ID: 33575571
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33584008
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
 

Author Comment

by:networkn
ID: 33608069
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
 
LVL 4

Assisted Solution

by:monicai
monicai earned 1000 total points
ID: 33608711
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
 
LVL 4

Assisted Solution

by:monicai
monicai earned 1000 total points
ID: 33608742
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
 

Author Comment

by:networkn
ID: 33805495
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
 

Author Comment

by:networkn
ID: 33979088
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
 

Author Comment

by:networkn
ID: 33979108
http://pastebin.com/H0VPpqeu

I have renamed a few of the details, but they remain consistent.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 34028576
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
 

Author Closing Comment

by:networkn
ID: 34176983
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question