What are the challenges you face when working across database platforms? Take the survey

How to create one email for all database backups?

robertgorobertgo Posts: 2
edited October 7, 2005 6:06AM in SQL Backup Previous Versions
I would be interested to know how you would go about creating one email for SQL Backup databases using the scripting capability using the command
'-SQL "BACKUP DATABASE TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full''

The command submits a single email for every database.
Am I missing an option here?


  • Options
    peteypetey Posts: 2,358 New member

    '-SQL "BACKUP DATABASE TO DISK = WITH NAME = [Database (' + @databasename + ') Full], MAILTO = [dba@acme.com] " '

    Note that you can use square brackets ([, ]) instead of single quotes in the script, to make things less confusing.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    SQL Backup can only send email regarding the database that it is currently backing up. If you wanted to consolidate reports and have them emailed to you, you would need to do something similar to the following script.

    Note -- I can't get the temp table to resolve to a single string -- maybe Peter can explain why this is?
    CREATE TABLE #tmpSQLBackupResults (Result nvarchar(128))
    INSERT #tmpSQLbackupresults EXEC master..sqlbackup '-SQL "BACKUP DATABASE [Northwind] TO DISK=''c:\temp\nwtest.bak'' WITH COMPRESSION=2, INIT" -E'
    /* EXAMPLE--do a second backup and append results to temp table */
    INSERT #tmpSQLbackupresults EXEC master..sqlbackup '-SQL "BACKUP DATABASE [Northwind] TO DISK=''c:\temp\nwtest.bak'' WITH COMPRESSION=2, INIT" -E'
    DECLARE @obMessage int
    DECLARE @iHREsult int
    exec @iHResult=SP_OACreate 'CDO.Message', @obMessage OUT
    exec @iHResult=SP_OASetProperty @obMessage, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2' /*cdoSendUsingPort*/
    exec @iHResult=SP_OASetProperty @obMessage, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '' /*server name*/
    exec @iHResult=SP_OASetProperty @obMessage, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value', '25' /*TCP port for SMTP */
    exec @iHResult=SP_OASetProperty @obMessage, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '0' /*cdoAnonymous*/
    exec @iHResult=SP_OAMethod @obMessage, 'Configuration.Fields.Update()'
    /* Set the properties on our message */
    exec @iHResult=SP_OASetProperty @obMessage, 'To', 'my@address.com' /* put your email here */
    exec @iHResult=SP_OASetProperty @obMessage, 'From', 'my@address.com' /* put your email here */
    exec @iHResult=SP_OASetProperty @obMessage, 'Subject', 'SQL Backup report'
    DECLARE cResult CURSOR FOR SELECT Result FROM #tmpSQLBackupResults
    DECLARE @Result char(128)
    DECLARE @ResultText char(4000)
    SET @ResultText='SQL Backup Results for multiple database backups'+CHAR(13)+CHAR(10)
    Open cResult
    FETCH cResult INTO @Result
    SELECT @ResultText=@ResultText+@Result+CHAR(13)+CHAR(10)
    FETCH cResult INTO @Result
    CLOSE cResult
    DEALLOCATE cResult
    exec @iHResult=SP_OASetProperty @obMessage, 'TextBody', @ResultText
    exec @iHResult=SP_OAMethod @obMessage, 'Send'
    drop table #tmpSQLbackupresults
    EXEC SP_OADestroy @obMessage
  • Options
    Here is a vb version that does work.

    Set conn = CreateObject("ADODB.Connection")
    conn.open "provider=SQLOLEDB;data source=(local);INITIAL CATALOG=master;ConnectTimeout=0;Trusted_Connection=Yes"
    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandTimeout=0 ' default is 30 seconds. Without setting this, large db backups will fail.
    cmd.CommandText="master..sqlbackup ' -SQL ""BACKUP DATABASE [Northwind] TO DISK = ''c:\Northwind_Full.sqb'' WITH NAME = ''Database (Northwind)'', INIT, ERASEFILES = 2, COMPRESSION = 1"" -E'"
    Set rs = cmd.Execute
    ret = ""
    While Not rs.EOF
    'Read each line of results and strip the NULLS that SQL Backup pads the line with
    ret = ret & REPLACE(rs(0), CHR(0), "") &vbCrLf
    Set cmd = Nothing
    Set conn = Nothing
    Daniel Handley
    Red Gate Software Ltd
Sign In or Register to comment.