SQLCMD Automation

[Nunos]

I am trying to write a script to backup a SQL DB that I need to automate. 

I have been told that it can be done in a batch file with the syntax below but the parameters may need changed.

SQLCMD -S.\SQLSERVER -E -Q "BACKUP DATABASE DBName TO DISK='C:\DBBackup\DBBackup.bak' WITH INIT" -oC:\DBBackup\DBBackupLog.log

So I tried writing the below which executes with a Exit Code = 0 but never actually does the backup. 

#RequireAdmin


$Server = ".\SQLServer"
$DBName = "TheDBName"
$Destination = "C:\DBBackup\DBBackup.bak"
$LogFile = "C:\DBBackup\DBBackup.Log"

Run("sqlcmd -S " & $Server  & ' -E ' & ' -Q "BACKUP DATABASE' & $DBName 'TO DISK=' & $Destination)

MsgBox(4096, "", "Done")

It also never displays the MsgBox. I have tried Run, RunWait, ShellExecute, and ShellExecuteWait.

Can someone please tell me what I am missing?

Thank you

[Danp2]

Do the following and I think you will start to see the problems

$Server = ".\SQLServer"
$DBName = "TheDBName"
$Destination = "C:\DBBackup\DBBackup.bak"
$LogFile = "C:\DBBackup\DBBackup.Log"

ConsoleWrite("sqlcmd -S " & $Server  & ' -E ' & ' -Q "BACKUP DATABASE' & $DBName 'TO DISK=' & $Destination & @crlf)

 

[Nunos]

Thank you for the help Danp2. 

 

==> Unable to parse line.:
ConsoleWrite("sqlcmd -S " & $Server & ' -E ' & ' -Q "BACKUP DATABASE' & $DBName 'TO DISK=' & $Destination & @crlf)
ConsoleWrite("sqlcmd -S " & $Server ^ ERROR
>Exit code: 1    Time: 0.04998

 

I am not knowledgeable enough to know what that means. Does it not like -E switch or the &'s?

[Danp2]

Ok.. there was a missing amperand. Try this --

$Server = ".\SQLServer"
$DBName = "TheDBName"
$Destination = "C:\DBBackup\DBBackup.bak"
$LogFile = "C:\DBBackup\DBBackup.Log"

ConsoleWrite("sqlcmd -S " & $Server  & ' -E ' & ' -Q "BACKUP DATABASE' & $DBName & 'TO DISK=' & $Destination & @crlf)

and then compare the output to your original command.

[Nunos]

(16) : ==> Unable to parse line.:
ConsoleWrite("sqlcmd -S " & $Server & ' -E ' & ' -Q "BACKUP DATABASE' & $DBName & 'TO DISK=' & $Destination & @crlf)
ConsoleWrite("sqlcmd -S " & $Server ^ ERROR
>Exit code: 1    Time: 0.04897
 

Same error. I also just noticed that I am missing part of the batch file in my script at the end of the destination name where it says WITH INIT" -oC:\DBBackup\DBBackupLog.log 

Would that require another & and single quotes around the WITH INIT then another & for the -o and then the log variable and quotes again?

I am in & and quote nightmare here. 

[Danp2]

First step would be to figure out why you are still getting this error. What version of AutoIt are you using? Are you running the full version of Scite?

[Nunos]

3.3.14.2 Version of AutoIT and just the built-in version of SciTe.

 

 

[Danp2]

Try installing the full SciTE.

[Nunos]

>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Scripts\DBBU.au3"   
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
sqlcmd -S .\SQLServer -E  -Q "BACKUP DATABASEthedbnameTO DISK=C:\DBBackup\DBBackup.bakWITH INIT
+>16:21:19 AutoIt3.exe ended.rc:0
+>16:21:19 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 0.4921

 

Found an extra space between the '-E ' and the &.

 

The above is the output. Should I next try to change the consolewrite to another option? If so what one do you suggest? 

 

UPDATE:

Tried Run and it seems to run but still no actual backup. 
 

Edited October 2, 2017 by Nunos

[Danp2]

Compare that to the original and you'll see some other space issues. 

[Nunos]

I didn't see anymore error's in the ConsoleWrite line do you see something? Should I be comparing it to the batch file where everything is shoved together or the AutoIT one I tried to write in the first post? 

[Danp2]

Yes... for me there were spaces missing before and after the database name. Also an issue with a missing double quote, which should get resolved when you fix the missing phrases at the end of the command.

[Nunos]

sqlcmd -S.\SQLServer -E  -Q "BACKUP DATABASE thedbname TO DISK=C:\DBBackup\DBBackup.bak WITH INIT+>12:16:54 AutoIt3.exe ended.rc:0

 

That is the result of the ConsoleWrite does that seem right? And the below is what I have now.

ConsoleWrite("sqlcmd -S"&$Server & ' -E ' & ' -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=' & $Destination & " WITH INIT")

 

 

[Danp2]

No, there's still the double quote issue. Try this --

ConsoleWrite("sqlcmd -S" & $Server & ' -E ' & ' -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=' & $Destination & ' WITH INIT"')

Basically, you are passing a string contained within the double quotes as the option for the -Q parameter.

[Jos]

Don't you need a space between -S and $Server?: 

ConsoleWrite('sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=' & $Destination & ' WITH INIT"')

Jos

Edited October 3, 2017 by Jos

[Danp2]

1 hour ago, Jos said:

Don't you need a space between -S and $Server?: 

He had it there earlier, but then removed it. FWIW, the initial command he listed in the OP did not have it present.

[Nunos]

I tried both above from Jos and Danp2 and both ConsoleWrite with rc:0 and when I replace ConsolWrite with Run they both execute but the backup is never created in the folder. 

[Danp2]

Have you tried executing the command manually from a command prompt? What was the exact command? Did the backup get created?

Have you tried checking the value of @error immediately after the Run command?

[aa2zz6]

Wouldn't it be easier to revise the example Microsoft gives to create an automated process to backup a database?

https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se

Edit: This one may be of use

https://blogs.msdn.microsoft.com/sqlagent/2010/10/12/create-a-database-backup-job-using-sql-server-management-studio/

Edited October 4, 2017 by aa2zz6

[Nunos]

Yes I have tried typing the cmd out into a command prompt and it works. I don't know how to do a check of @error but I am happy to try if you tell me the steps.