Help with Backup Utility script

[TheGreaterAjax]

Hi there! First post here, so bear with me. Myself and a co-worker are working on a utility that will backup client's POS databases daily, to avoid data loss in the event of some sort of disaster. We thought we were on the right track and had something working but, after further inspection we noticed that settings from an .ini file are not being passed through to the SQL command that is to run the creation of said backup.

This program is written with the intent of letting an end user select where they would like to store the backup files on their external/secondary drive/etc, but also create backups of backups in AppData where techs are able to find them failing the first method. Second, after configuring the locations of all of these settings it writes and .ini file for later use, so when scheduled for whenever they would like the backup to be done (daily, weekly, monthly, etc.), it will only run the SQL backup command and skip the GUI.

I've renamed everything pertaining to what could be considered customer/company information to something generic. If someone could please take a look at this and throw about some ideas on improvements/ways to pass variables to the SQL command, I'd be very grateful!

Thanks, and have a great day!

 

Ajax

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=Test Backup Utility.exe
#AutoIt3Wrapper_Outfile_x64=Test Bacukup Utilityx64.exe
#AutoIt3Wrapper_Compile_Both=y
#AutoIt3Wrapper_UseX64=y
#AutoIt3Wrapper_Res_Fileversion=0.0.0.1
#AutoIt3Wrapper_Res_Fileversion_AutoIncrement=p
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
;#RequireAdmin
#include <MsgBoxConstants.au3>
#include <Constants.au3>
#include <Array.au3>
#include <file.au3>
#include <date.au3>
#include <GUIConstants.au3>

Global $Source = '', $Destination = ''

$Server = ".\Test"                                                                                                                              ; This will need an input box and variable for the SQL Server name
$DBName = "Test"                                                                                                                                ; This will need an input box and variable for the database instance name
$Destination = "C:\TestBackup\TestBackup.bak"                                                                                                   ; This will need the browse button and input box with a variable for the backup location
$LogFile = "C:\TestBackup\TestBackup.Log"                                                                                                       ; Might do same as above or make our own location in appdat? or wherever the backup is stored maybe even IDS
$LogFileName = @AppDataDir & "\ParentFolder\Backup.log"

If Not FileExists("C:\TestBackup\TestBackup.bak") Then DirCreate("C:\TestBackup\")                                                              ; Creates backup directory if it doesn't exist
If Not FileExists(@AppDataDir & "\ParentFolder\TestBackup.ini") Then DirCreate(@AppDataDir & "\ParentFolder\")                                  ; Creates Parent folder where a backup log and  the .ini files are housed
If Not FileExists(@AppDataDir & "\ParentFolder\TestBackup.ini") Then GUI()                                                                      ; If there is no parent folder, then run the GUI created below to configure settings

_log('========== Starting Test Backup APP ==========')




run(@ComSpec & ' /c sqlcmd -S ' & $Server & ' -E  -Q "BACKUP DATABASE ' & $DBName & ' TO DISK=''' & $Destination & ''' WITH INIT" -o ' & $LogFile,@ScriptDir,@SW_HIDE)              ;----> Command that I'm fairly sure is causing the issue

Sleep(5000)


FileOpen($LogFile,0)
$LogCopy = FileRead($LogFile)
_log('Backup - ' & $LogCopy)

_exit()


func _exit()                                                                                                                                    ; This is the Exit function
    _log('********** Ending Test Backup APP **********')                                                                                        ; Appends to the text file
    exit
endfunc

func _log($str)                                                                                                                                 ; The log file function
    local $hfl = fileopen($LogFileName,1)                                                                                                       ; Opens the file and assigns the variable
    if $hfl = -1 Then                                                                                                                           ; Error catching
        msgbox(0,'','Log file failed to open.' & @crlf & 'File name = ' & $LogFileName)                                                         ; If Log doesn't open
        Exit
    EndIf
    filewrite($hfl,_now() & ' ' & $str & @CRLF)                                                                                                 ; Writes date, time, and the string that should be the name of the files.
    fileclose($hfl)                                                                                                                             ; Closes the file
endfunc

Func GUI()

   Local $msg
   Local $btnOkay
   Local $btnClose
   Local $btnBrowse

   $GUI = GUICreate("Test Backup", 310, 280)                                                                                                    ; Will create a dialog box that when displayed is centered

   GUICtrlCreateLabel("Please input your server name starting with '.\'",15,25)                                                                 ; Creates label for server name input box
    $inpBoxSrvName = GUICtrlCreateInput(".\Test", 15, 45, 150, 20)                                                                              ; Creates input box & populates with ".\Test"
   GUICtrlCreateLabel("Please input your database name", 15, 75)                                                                                ; Creates label for database name input box
    $inpBoxDbName = GUICtrlCreateInput("Test", 15, 95, 150, 20)                                                                                 ; Creates input box & populates with "Test"

   GUICtrlCreateLabel("Please select a destination for your backup", 15, 125)                                                                   ; Creates label for backup destination input
    $btnBrowseDest = GUICtrlCreateButton("Browse", 235, 125, 65, 25)                                                                            ; Button for destination of backup & populates text with "Browse"
    $inpBoxDest = GUICtrlCreateInput("", 15, 145, 150, 20)                                                                                      ; Input box for destination of backup


   GUICtrlCreateLabel("Please select a destination of the log file", 15, 175)                                                                   ; Creates label for log file destination
    $btnBrowseLog = GUICtrlCreateButton("Browse", 235, 175, 65, 25)                                                                             ; Button for destination of log file & populates text with "Browse"
    $inpBoxLog = GUICtrlCreateInput("", 15, 195, 150, 20)                                                                                       ; Input box for destination of log file

    $btnOkay = GUICtrlCreateButton("Okay", 65, 245, 85, 25)                                                                                     ; Okay button
    $btnClose = GUICtrlCreateButton("Close", 165, 245, 85, 25)                                                                                  ; Close button

   GUISetState()

      While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE                                                                                                        ; Close button logic
                ExitLoop
            Case $msg = $btnBrowseDest                                                                                                          ; Logic for selecting the folder destination for backup
               $Path = FileSelectFolder('Select destination for your backup.', 'C:\', 2, $Source, $GUI)
                  If $Path Then
                     GUICtrlSetData($inpBoxDest, $Path)
                     $Source = $Path
                  EndIf
            Case $msg = $btnBrowseLog                                                                                                           ; Logic for selecting the folder destination for your log file
               $Path = FileSelectFolder('Select destination for your log file.', 'C:\', 2, $Source, $GUI)
                  If $Path Then
                     GUICtrlSetData($inpBoxLog, $Path)
                     $Source = $Path
                  EndIf
            Case $msg = $btnOkay                                                                                                                ; When "Okay" button is clicked, write settings to .ini file for later use
                 ;If Not FileExists("C:\TestBackup\TestBackup.ini") Then DirCreate("C:\TestBackup\")                                            . Should be created above so not needed?
                 IniWrite(@AppDataDir & "\ParentFolder\TestBackup.ini", "Server Name", "Name", GUICtrlRead($inpBoxSrvName))                     ; Setting for Server
                 IniWrite(@AppDataDir & "\ParentFolder\TestBackup.ini", "Database Name", "DbName", GUICtrlRead($inpBoxDbName))                  ; Setting for Database
                 IniWrite(@AppDataDir & "\ParentFolder\TestBackup.ini", "Backup Destination", "Path", GUICtrlRead($inpBoxDest))                 ; Location of where backup folder is located
                 IniWrite(@AppDataDir & "\ParentFolder\TestBackup.ini", "Log File Destination", "Path", GUICtrlRead($inpBoxLog))                ; Location of where backup folder is located

                 ;$Server = IniRead("C:\TestBackup\TestBackup.ini", "Server Name", "Name", "default")                                           . Should be reading in the SQL command above, but isn't?
                 ;$DBName = IniRead("C:\TestBackup\TestBackup.ini", "Database Name", "DbName", "default")                                       . Should be reading in the SQL command above, but isn't?
                 ;$Destination = IniRead("C:\TestBackup\TestBackup.ini", "Backup Destination", "Path", "default")                               . Should be reading in the SQL command above, but isn't?
                 ;$LogFile = IniRead("C:\TestBackup\TestBackup.ini", "Log File Destination", "Path", "default")                                 . Should be reading in the SQL command above, but isn't?

                ExitLoop
            Case $msg = $btnClose
                ExitLoop
        EndSelect
      WEnd

EndFunc

 

[Danyfirex]

Hello. You're question is no clear enough. I think you need to try writing your commandline parameter to check if it work correctly when you use it manually maybe you have a typo error. You need to debug your script step by step to make sure where the issue is residing.

 

Saludos

[TheGreaterAjax]

Sorry, I'll try to be more concise.

The GUI collects the bits of data and stores them in variables. Those variables being "$Server", "$DBName", "$Destination", & "$LogFile". These variables work when initially creating all of the information. However, when trying to pass these to the SQL command, is where it is hanging up I believe.

[spudw2k]

For troubleshooting purposes you can change "/c" to "/k" in the Run function.  This should keep the console window open after executing sqlcmd. and will allow you to see if any errors are returned and validate the command string looks correct.

[Earthshine]

when you manually run the backup queries, does it work correctly?

Edited November 27, 2017 by Earthshine