How to create one email for all database backups?

• 
  MAILTO, or MAILTO_ONERROR, eg
  
  '-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.

  petey August 26, 2005 11:47

  - 0 +
• 
  Hello,
  
  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', '127.0.0.1' /*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
  WHILE @@FETCH_STATUS != -1
  BEGIN
  SELECT @ResultText=@ResultText+@Result+CHAR(13)+CHAR(10)
  FETCH cResult INTO @Result
  END
  CLOSE cResult
  DEALLOCATE cResult
  exec @iHResult=SP_OASetProperty @obMessage, 'TextBody', @ResultText
  exec @iHResult=SP_OAMethod @obMessage, 'Send'
  drop table #tmpSQLbackupresults
  
  EXEC SP_OADestroy @obMessage
  

  Brian Donahue August 26, 2005 11:48

  - 0 +
• 
  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
  rs.MoveNext
  WEnd
  MsgBox(ret)
  rs.close
  Set cmd = Nothing
  Set conn = Nothing

  Daniel Handley October 07, 2005 06:06

  - 0 +

Add comment

Please sign in to leave a comment.