Comments
Sort by recent activity
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
/ comments
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 M...
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
/ comments
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 th...
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.
/ comments
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 somethin...
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
/ comments
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...
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.
/ comments
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. H...
Fred,
email me and I can send you a script that might be similar to the one you are looking for. I have a script that queries a folder to get a list of database backups, stores the list in a recordset, and loops through the recordset, using SQLBackupC to restore the databases. It also runs scripts to change some tables because the databases are being restored on a different server, and uses the "With Move" option for that same reason. I'd post it here, but it is long.
Aaron / comments
Fred,
email me and I can send you a script that might be similar to the one you are looking for. I have a script that queries a folder to get a list of database backups, stores the list in a record...
Ok. Let me just change some database names and I'll send it in.
Daniel Handley wrote:
If you send the script in to support(at)red-gate.com i can always post it on our ftp site for retrieval.
It would also be useful for us to have to give out to other cutomers.
Thanks
Dan
/ comments
Ok. Let me just change some database names and I'll send it in.
Daniel Handley wrote:
If you send the script in to support(at)red-gate.com i can always post it on our ftp site for retrieval.
It wo...
Great, thanks!
petey wrote:
I'll add it to the feature request list for future consideration. Thanks for letting us know.
/ comments
Great, thanks!
petey wrote:
I'll add it to the feature request list for future consideration. Thanks for letting us know.
Thanks, you answered it for me. Basically, I have a script that uses SQLCMD to restore databases, and then uses SQLCMD to call .SQL files that make updates to the databases. The .SQL files aren't just simple SQL commands, they're complex scripts that are about 100 lines of code each. That's why they're stored in .SQL files rather than just executing the commands directly with SQLCMD - there is a lot of processing that goes on in the scripts as well. I was easily able to replace the restore part with SQLBackupC. I was hoping to call the .SQL files with SQLBackupC as well, so I would only have to use one program. It's not a big deal though. SQLBackupC does the restore and SQLCMD calls the scripts.
eddie davis wrote:
Hi
Sorry I am a little confused with your question.
Red Gate SQL Backup provides a comand line utility so you can perform back and restore operations in any SQL Script or batch files that you create.
The SQL Backup command line executable is call SQLBackupC. It is located in the folder in which you installed the SQL Backup Server Components, by default this is C:\Program Files\Red Gate\SQL Backup\<instance name>.
The SQL Backup command line interface uses a subset of the T-SQL Backup and Restore statements.
Further advice on using the SQL Backup Command Line Interface can be found in the SQL Backup Help file.
If I have understood your post correctly, the answer is no you cannot use the SQL Backup Command Line Interface to call other .SQL Scripts.
However you can include SQL Backup Command Line Interface into any .SQL Scripts that you create.
Eddie Davis
Red Gate Software Ltd
Technical Support Engineer
/ comments
Thanks, you answered it for me. Basically, I have a script that uses SQLCMD to restore databases, and then uses SQLCMD to call .SQL files that make updates to the databases. The .SQL files aren't j...