SQLBackupC - Mailto

aarons44 · August 17, 2007 2:57PM

I was wondering what settings SQLBackupC uses for sending mail with the mailto option? Does it use the settings specified in the GUI, or do you need to specify the mail server in the command?

petey · August 17, 2007 11:05PM

The command line interface uses the settings you specified using the GUI, thus you do not need to provide the mail server settings.

One difference when using the command line interface is that all the work will be performed using the credentials of the logged-on user. When using the extended stored procedure, all work is performed using the credentials of the SQL Backup service startup user.

aarons44 · August 20, 2007 2:38PM

Thanks for the help. I think I've figured out the problem now. Our mail servers block emails that don't have a FQN in the From field. The default for SQLBackup uses SQLBackup@servername or something like that. I changed it to servername@ourdomain.com and it worked fine. I thought at first maybe I needed to specify the parameters at the command line. The test emails worked from the GUI, and in a couple of hours the SQLBackupC jobs will have run, and I'll know if it working properly or not.

Thanks!

petey wrote:

The command line interface uses the settings you specified using the GUI, thus you do not need to provide the mail server settings.

One difference when using the command line interface is that all the work will be performed using the credentials of the logged-on user. When using the extended stored procedure, all work is performed using the credentials of the SQL Backup service startup user.

aarons44 · August 22, 2007 3:32PM

I still can't get the command line MAILTO option to work. I'm calling the restore query from VB. It gets stored in a variable, and then later I call SQLBackupC with the variable as the parameter. Here's the line of code:

query10 = "-SQL ""RESTORE DATABASE " & myString6 & " FROM DISK = 'G:\myFiles\CompressedBackups" & myFile6 & "' WITH MOVE '" & myString6 & "' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".mdf', MOVE '" & myString6 & "_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".ldf' WITH MAILTO='myemail@mydomain.com'"" -I sql -U user -P password THREADPRIORITY=6"

The problem must be with where the mailto option is placed, because the restore works. Here I get a syntax error. I've also tried putting it right before the -I and I don't get an error, but I don't get the mail either. Test emails from the GUI work fine. The mail server doesn't require authentication, so credentials shouldn't be an issue.

petey wrote:

The command line interface uses the settings you specified using the GUI, thus you do not need to provide the mail server settings.

One difference when using the command line interface is that all the work will be performed using the credentials of the logged-on user. When using the extended stored procedure, all work is performed using the credentials of the SQL Backup service startup user.

Jason Cook · August 23, 2007 6:32AM

Hi,
The generated command will not function correctly because of confusion between the various tags.

Any part of the SQL Backup command needs to go inside the -SQL "..." tag, and the -I, -U and -P tags come afterwards.

So in your example, this should be:

query10 = "-SQL ""RESTORE DATABASE " & myString6 & " FROM DISK = 'G:\myFiles\CompressedBackups\" & myFile6 & "' WITH MOVE '" & myString6 & "' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" & myString6 & ".mdf', MOVE '" & myString6 & "_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" & myString6 & ".ldf' WITH MAILTO='myemail@mydomain.com', THREADPRIORITY=6"" -I sql -U user -P password "

Hope that helps,
Jason

aarons44 · August 23, 2007 10:17AM

When I try that I get a syntax error, and SQL Backup exit code: 500.

Jason Cook wrote:

Hi,
The generated command will not function correctly because of confusion between the various tags.

Any part of the SQL Backup command needs to go inside the -SQL "..." tag, and the -I, -U and -P tags come afterwards.

So in your example, this should be:

query10 = "-SQL ""RESTORE DATABASE " & myString6 & " FROM DISK = 'G:\myFiles\CompressedBackups" & myFile6 & "' WITH MOVE '" & myString6 & "' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".mdf', MOVE '" & myString6 & "_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".ldf' WITH MAILTO='myemail@mydomain.com', THREADPRIORITY=6"" -I sql -U user -P password "

Hope that helps,
Jason

aarons44 · August 23, 2007 10:32AM

Ok, you got me pointed in the right direction and I got it figured out. It was:
query10 = "-SQL ""RESTORE DATABASE " & myString6 & " FROM DISK = 'G:\myFiles\CompressedBackups" & myFile6 & "' WITH MOVE '" & myString6 & "' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".mdf', MOVE '" & myString6 & "_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".ldf', MAILTO='myemail@mydomain.com', THREADPRIORITY=6 "" -I sql -U user -P password "

The comma before the MAILTO was needed to make it a continuation of the previous WITH statement. Thanks for your help. It was really confusing getting the single quotes, double quotes, two sets of double quotes, etc. all figured out to make this work.

Jason Cook wrote:

Hi,
The generated command will not function correctly because of confusion between the various tags.

Any part of the SQL Backup command needs to go inside the -SQL "..." tag, and the -I, -U and -P tags come afterwards.

So in your example, this should be:

query10 = "-SQL ""RESTORE DATABASE " & myString6 & " FROM DISK = 'G:\myFiles\CompressedBackups" & myFile6 & "' WITH MOVE '" & myString6 & "' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".mdf', MOVE '" & myString6 & "_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" & myString6 & ".ldf' WITH MAILTO='myemail@mydomain.com', THREADPRIORITY=6"" -I sql -U user -P password "

Hope that helps,
Jason

Jason Cook · August 23, 2007 10:59AM

Indeed, my mistake... didn't spot that you had two "WITH" statements as well. Of course should only have a maximum of one.

Not sure what version of VB you are using, but if you are using .NET, you could use the String.Format function to try and make the code easier to understand (if you have to do this again in the future):

For example, your code would become something like:

query10 = String.Format(@ -SQL "RESTORE DATABASE {0} FROM DISK = 'G:\myFiles\CompressedBackups\{1}' WITH MOVE '{0}' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\{0}.mdf', MOVE '{0}_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\{0}.ldf', MAILTO='myemail@mydomain.com', THREADPRIORITY=6 "" -I sql -U user -P password",myString6,myFile6);

Granted it's still not ideal, but it's eliminated a significant number of the double quotes... so seems slightly less complicated. The following link may be useful (mentions C#, but should be the same in VB.net): http://blog.stevex.net/index.php/string ... in-csharp/

Hope that helps,
Jason

aarons44 · August 23, 2007 11:59AM

thank you for the suggestions and links. My script is definetly not VB.Net. I'm behind in my learning. But if I get into a lot more scripting, I would like to get up to date with the new options that are available with the newer scripting languages.

Jason Cook wrote:

Indeed, my mistake... didn't spot that you had two "WITH" statements as well. Of course should only have a maximum of one.

Not sure what version of VB you are using, but if you are using .NET, you could use the String.Format function to try and make the code easier to understand (if you have to do this again in the future):

For example, your code would become something like:

query10 = String.Format(@ -SQL "RESTORE DATABASE {0} FROM DISK = 'G:\myFiles\CompressedBackups\{1}' WITH MOVE '{0}' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\{0}.mdf', MOVE '{0}_LOG' TO 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\{0}.ldf', MAILTO='myemail@mydomain.com', THREADPRIORITY=6 "" -I sql -U user -P password",myString6,myFile6);

Granted it's still not ideal, but it's eliminated a significant number of the double quotes... so seems slightly less complicated. The following link may be useful (mentions C#, but should be the same in VB.net): http://blog.stevex.net/index.php/string ... in-csharp/

Hope that helps,
Jason

SQLBackupC - Mailto

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk