I have looked and searched to find a way to backup sql server db and send the backup file to email address with sql server mail profile but i did not get it, I found about sending email when there is a failure in db, and other notification but i did not see about backup and sending it to email automatically, is there any way to create a job to do that ? Thanks.
Asked
Active
Viewed 2,696 times
5
-
How will you mail for example 5+ GB database backup? – Lukasz Szozda Sep 03 '15 at 12:08
-
3good question i did not mentioned that because my db size is small. – Younis Qadir Sep 03 '15 at 12:11
-
Isn't it a better option to create a shared folder in your network, place the backup there and email the path?? – d_luffy_de Sep 03 '15 at 12:17
-
yes it is one of the solutions , I've wondered to see if there is a way to do with email or not. thanks for your helps. – Younis Qadir Sep 03 '15 at 12:22
1 Answers
6
If you double click that SQL Agent job, go to the Steps section add another step there and execute the following procedure there.. Also make sure the following step is executed after the database backup step.
Exec sp_send_dbmail
@profile_name = 'profile_name'
, @recipients = 'ablabla@abc.com'
, @subject = 'Database Backups bla bla'
, @body = 'body'
, @file_attachments = 'B:\Backups\DB_Back.bak'
This will send an email out to the person with the backup file as an attachment. Also by default sql server has a limit on how big file you can send you can change them settings to send a bigger file.
Consult your data protection team before you do this, I dont think any company would allow this to happen :) just a heads up
M.Ali
- 67,945
- 13
- 101
- 127
-
1this is what i want thanks for your answer , i am going to test it , and don't worry about security it is for a small business there no sensitive data. – Younis Qadir Sep 03 '15 at 12:43