Nunos

SQLCMD Automation

30 posts in this topic

#1 ·  Posted

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

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)

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

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?

Share this post


Link to post
Share on other sites

#4 ·  Posted

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.

Share this post


Link to post
Share on other sites

#5 ·  Posted

(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. :sweating:

Share this post


Link to post
Share on other sites

#6 ·  Posted

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?

Share this post


Link to post
Share on other sites

#7 ·  Posted

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

 

 

Share this post


Link to post
Share on other sites

#8 ·  Posted

Try installing the full SciTE.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

>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 by Nunos

Share this post


Link to post
Share on other sites

#10 ·  Posted

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

Share this post


Link to post
Share on other sites

#11 ·  Posted

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? 

Share this post


Link to post
Share on other sites

#12 ·  Posted

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.

Share this post


Link to post
Share on other sites

#13 ·  Posted

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")

 

 

Share this post


Link to post
Share on other sites

#14 ·  Posted

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.

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by Jos

Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

#16 ·  Posted

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.

Share this post


Link to post
Share on other sites

#17 ·  Posted

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. 

Share this post


Link to post
Share on other sites

#18 ·  Posted

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?

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

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 by aa2zz6

Share this post


Link to post
Share on other sites

#20 ·  Posted

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now