Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Attachment using backup Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 1:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 141, Visits: 1,116
I can setup a script using sp_send_db_mail to include the attachment of a backup job But I can't include an attachment if I use the the backup wizard correct?
Post #1562831
Posted Thursday, April 17, 2014 8:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 15,489, Visits: 27,875
No, a backup needs to be restored. You can only attach a database that has been detached (or at least shut down).

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1562896
Posted Thursday, April 17, 2014 8:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 141, Visits: 1,116
I don't know if you understand what I was asking. If you run a command backup database dbname ......... , when it's done you see information as such "Processed 0 pages for database
'DBName', on file 1.BACKUP LOG successfull........" I can script the backup job & use db_send_mail to include the attachment but If I create the backup job using the maintenance plan can I have it send the email with the attachment to the operator.
Post #1562903
Posted Friday, April 18, 2014 4:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 15,489, Visits: 27,875
Oh, I don't know. I don't use SQL Server to send emails except for alerts. If I had to do that, I'd probably go to Powershell for it anyway. There's no reason why you couldn't include a backup in an email from there, although that's potentially a big file to be emailing around.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1562961
Posted Friday, April 18, 2014 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:57 AM
Points: 7,112, Visits: 6,280
Depends on how big the backup is. You could use an Execute T-SQL Statement Task to send the email, but I think there's a size limitation on the attachment using this method and it's not very large. Though the issue could be the size limitation in Outlook rather than in SSIS (the base for the maintenance plan wizard).

I would not send a backup file via email anyway. Too easy for it to get corrupted or intercepted or just to plain fail due to backup size. Use a NAS share that only a select few have access to and copy it there. That way you can control access and it is less likely to cause other issues.

I certainly wouldn't want to receive an attachment that large in my inbox. Outlook would freeze while trying to process that.

EDIT: From BOL about sp_send_dbmail - By default, Database Mail limits file attachments to 1 MB per file. For more information, see Database Mail Configuration Wizard.

EDIT 2: It occurs to me you might be talking about the log instead of the actual backup file. If the log, then usually those aren't big. You could send that using the Execute T-SQL Statement Task.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse