Solved

SQL Express Backup and Email Notification

Posted on 2010-08-29
14
1,000 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
  • 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 250 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Assisted Solution

by:monicai
monicai earned 250 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 250 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 250 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now