How to create one email for all database backups?
robertgo
Posts: 2
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?
'-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?
Comments
'-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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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?
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
Red Gate Software Ltd