How to create one email for all database backups?

Comments

• petey Redgate › Posts: 2,358 New member

  August 26, 2005 11:47AM

  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.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• Brian Donahue Posts: 6,590 Bronze 1

  August 26, 2005 11:48AM

  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
  

  0 · Share on Twitter
• Daniel Handley Posts: 214

  October 7, 2005 6:06AM

  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
  Red Gate Software Ltd

  0 · Share on Twitter