SQLCMD Automation

[Jos]

I would start with trying to run the command like this and see what happens:

run(@ComSpec & ' /k SQLCMD -S.\SQLSERVER -E -Q "BACKUP DATABASE DBName TO DISK=''C:\DBBackup\DBBackup.bak'' WITH INIT"')

When that works we can start building it out with the variables.

Jos

[Nunos]

That opened up a cmd console and ran the only thing it didn't do was close and log, but I suspect that is because the -o switch was omitted. So is it possible my variables are not passing through properly or perhaps that cmd window is getting stuck??? 

[Jos]

Nope, the intent was for it to stay open to see any errors and that is why it has a /k at the start. /c will close that window, but that is of later concern.

So now with the variables in it which I assume you have define in your script:

run(@ComSpec & ' /k sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=''' & $Destination & ''' WITH INIT"')

Jos

[Nunos]

Yes that seems to have worked as well except for the log file. 

 

[Jos]

So have you tried adding that to the working commandline?   if so and not working then please show what you have. 

Jos

[Nunos]

run(@ComSpec & ' /k sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=''' & $Destination & ''' WITH INIT"' '-o' &$LogFile' )

I did try but it didn't work. The ' placing doesn't make sense yet to me. Do I need to add one each time I pass a variable in like $Logfile ? And do they need to both proceed and follow? Very confusing to me at the moment I apologize for all the questions.

 

[Jos]

Try this:

run(@ComSpec & ' /k sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=''' & $Destination & ''' WITH INIT" -o ' & $LogFile )

unsure whether you need a space between -o and the filename, just try it both in case this doesn't work.

Jos

[Nunos]

It works. I also changed the @comspec /k to /c and the cmd console vanishes after the script executes.

 

Thank you for the assistance!!! Do you know what I can do in the documentation to learn about the apostrophe and ampersand rules? 

 

[Jos]

You are welcome.

As to learning about the proper use of single and double quotes. It helps when you use SciTE with its syntax highlighting and read up on it in the Help file. The rest is a matter of practice and taking steps like it did with you today.

When you don't want to see any window at all, you could set the 3rd parameter of the run()command like:

run(@ComSpec & ' /c sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=''' & $Destination & ''' WITH INIT" -o ' & $LogFile,Default,@SW_HIDE)

Have fun,
Jos

[Nunos]

Awesome I will add the @SW_Hide for sure. Thank you again so much for the help.