Luigi Micco homepage

vbPDF, vbPDFParser, vbQRCode, vbDataMatrix, vbPDF417, vbAztec, clsPDFCreator, vbGCalendar

Strumenti Utente

Strumenti Sito


Barra laterale

I miei progetti

Donazioni

Se quello che hai trovato in queste pagine ti e' stato in qualche modo d'aiuto, puoi contribuire con una donazione!

Donazioni

oppure puoi cliccare su uno dei banner presenti qui.

autoit:sqlsupport
Traduzioni di questa pagina: it en 

  • Esporta in OpenOffice
  • Esporta in PDF
  • Agg./Rimuovi selezione
  • Strumenti:

SQLSupport

SQLSupport è un programma per la gestione dei backup e ripristino dei database di istanze Microsoft SQL Server, con una interfaccia semplice ed intuitiva.

Screenshot

SQLSupport

Codice sorgente

sqlsupport.au3
#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=database.ico
#AutoIt3Wrapper_Outfile=SQLSupport.exe
#AutoIt3Wrapper_Outfile_x64=SQLSupport_x64.exe
#AutoIt3Wrapper_Compression=4
#AutoIt3Wrapper_UseUpx=Y
#AutoIt3Wrapper_Res_Comment=SQLSupport
#AutoIt3Wrapper_Res_Description=Sistema di backup e ripristino per MS SQL Server
#AutoIt3Wrapper_Res_Fileversion=1.0.0.0
#AutoIt3Wrapper_Res_LegalCopyright=Luigi Micco
#AutoIt3Wrapper_Res_SaveSource=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
 
; #SQLSupport# ==================================================================================================================
; Title .........: SQLSupport
; AutoIt Version : 3.3.10.2
; Description ...:Sistema di backup e ripristino per istanze MS SQL Server
; Author(s) .....: Luigi Micco
; Notes .........:
; ===============================================================================================================================
 
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiButton.au3>
#include <GuiEdit.au3>
#include <Inet.au3>
#include <File.au3>
#include <Array.au3>
#include <GUIComboBox.au3>
#include <Process.au3>
#include <Date.au3>
#include <TabConstants.au3>
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
 
 
If Not IsAdmin() Then
	MsgBox($MB_SYSTEMMODAL, "", "Questo programma deve essere eseguito con i permessi di amministratore.")
	Exit
EndIf
 
Global $gInifile = "sqlsupport.ini"
Global $gServer = ""
Global $gUser = ""
Global $gPassword = ""
Global $gDebug = ""
 
Global $gBackupFolder = ""
Global $gDaysToKeep = ""
 
Global $gScheduled = ""
Global $gScheduleTime = ""
 
Global $gCurrentTime = _NowCalc()
 
Call("LoadSettings")
 
If $CmdLine[0] > 0 Then
	If StringUpper($CmdLine[1]) = "/H" Then
 
		If $gScheduled = "1" Then
			While Sleep(500)
				$a = _NowTime()
				If $a = $gScheduleTime Then
					Call("backup")
				EndIf
			WEnd
		Else
			Call("backup")
		EndIf
	EndIf
 
Else
 
	AutoItSetOption("TrayIconHide", 1)
 
 
	$Form1 = GUICreate("SQLSupport :: Sistema di backup e ripristino per MS SQL Server", 700, 360, 339, 93)
	GUISetFont(12)
 
	GUICtrlCreateLabel("Server:", 10, 6, 90, 25)
	Global $inpServer = GUICtrlCreateInput($gServer, 10, 25, 300, 22)
	GUICtrlCreateLabel("Utente:", 10, 50, 250, 22)
	Global $inpUser = GUICtrlCreateInput($gUser, 10, 70, 150, 22)
	GUICtrlCreateLabel("Password:", 10, 95, 250, 22)
	Global $inpPassword = GUICtrlCreateInput($gPassword, 10, 115, 150, 22, BitOR($ES_PASSWORD, $ES_AUTOHSCROLL))
	_GUICtrlEdit_SetPasswordChar($inpPassword, "*")
 
	GUICtrlCreateButton("Verifica connessione !", 330, 25, 170, 50)
	GUICtrlSetOnEvent(-1, "CheckConnection")
 
 
	; TAB
	GUICtrlCreateTab(10, 150, 500, 200)
	GUICtrlCreateTabItem("Backup")
	GUICtrlSetState(-1, $GUI_SHOW) ; Use this!
	; -- Tab 1
 
	GUICtrlCreateLabel("Cartella destinatione backup:", 20, 180, 200, 20)
	Global $inpBackupFolder = GUICtrlCreateInput($gBackupFolder, 20, 200, 400, 22)
 
	GUICtrlCreateButton("...", 425, 200, 40, 22)
	GUICtrlSetOnEvent(-1, "BackupBrowseClick")
 
	; UPDOWN
	GUICtrlCreateLabel("Giorni da conservare:", 20, 230)
	Global $inpDaysToKeep = GUICtrlCreateInput($gDaysToKeep, 200, 230, 50, 22)
	GUICtrlCreateUpdown(-1)
	GUICtrlSetLimit(-1, 31, 0) ; to limit the entry to 3 chars
 
	GUICtrlCreateLabel("Schedula:", 20, 260, 180, 22)
	Global $inpScheduled = GUICtrlCreateCheckbox("", 200, 260, 50, 22)
	If $gScheduled = "1" Then GUICtrlSetState($inpScheduled, $GUI_CHECKED)
 
	; UPDOWN
	GUICtrlCreateLabel("Orario:", 20, 290)
	Global $inpScheduleTime = GUICtrlCreateDate($gScheduleTime, 200, 290, 70, 22, $DTS_TIMEFORMAT)
 
	; to select a specific default format
	$DTM_SETFORMAT_ = 0x1032 ; $DTM_SETFORMATW
	$style = "HH:mm"
	GUICtrlSendMsg($inpScheduleTime, $DTM_SETFORMAT_, 0, $style)
 
	GUICtrlCreateLabel("Notifiche:", 20, 320, 180, 22)
	Global $inpDebug = GUICtrlCreateCheckbox("", 200, 320, 50, 22)
	If $gDebug = "1" Then GUICtrlSetState($inpDebug, $GUI_CHECKED)
 
	GUICtrlCreateButton("Copia ora !", 400, 290, 100, 50)
	GUICtrlSetOnEvent(-1, "backupnow")
 
	; -- Tab 2
	GUICtrlCreateTabItem("Ripristino")
 
 
	GUICtrlCreateLabel("Cartella da ripristinare:", 20, 180, 200, 20)
	Global $inpRestoreFolder = GUICtrlCreateInput("", 20, 200, 400, 22)
	GUICtrlCreateButton("...", 425, 200, 40, 22)
	GUICtrlSetOnEvent(-1, "RestoreBrowseClick")
 
	GUICtrlCreateButton("Ripristina !", 400, 290, 100, 50)
	GUICtrlSetOnEvent(-1, "restore")
 
	; -- End Tab
	GUICtrlCreateTabItem("")
 
	GUICtrlCreateButton("Uscita", 530, 10, 160, 30)
	GUICtrlSetOnEvent(-1, "exitt")
 
	GUICtrlCreateButton("Info ...", 530, 41, 160, 30)
	GUICtrlSetOnEvent(-1, "about_program")
 
	GUICtrlCreateButton("Salva configurazione", 530, 72, 160, 30)
	GUICtrlSetOnEvent(-1, "SaveSettings")
 
	GUICtrlCreateButton("Autorun ...", 530, 134, 160, 30)
	GUICtrlSetOnEvent(-1, "autorun")
 
	$handle = WinGetHandle($Form1)
 
	Opt("GUIOnEventMode", 1)
	GUISetOnEvent($GUI_EVENT_CLOSE, "exitt")
 
	GUISetState(@SW_SHOW) ; Move this!
 
	Local $msg
	Do
		$msg = GUIGetMsg()
	Until $msg = $GUI_EVENT_CLOSE
 
EndIf
 
Func about_program()
	MsgBox(64, "SQLSupport :: Info ...", @LF & "Autore: Luigi Micco" & @LF & "Email: [email protected]" & @LF & "Versione: 1.0.0")
EndFunc   ;==>about_program
 
Func exitt()
	Exit
EndFunc   ;==>exitt
 
Func BackupBrowseClick()
	$strSavePath = FileSelectFolder("Selezionare un disco o una cartella", "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}", 7)
	GUICtrlSetData($inpBackupFolder, $strSavePath)
EndFunc   ;==>BackupBrowseClick
 
Func RestoreBrowseClick()
	$strSavePath = FileSelectFolder("Selezionare un disco o una cartella", "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}", 6)
	GUICtrlSetData($inpRestoreFolder, $strSavePath)
EndFunc   ;==>RestoreBrowseClick
 
Func SaveSettings()
	$server = GUICtrlRead($inpServer)
	$user = GUICtrlRead($inpUser)
	$password = GUICtrlRead($inpPassword)
	$backupfolder = GUICtrlRead($inpBackupFolder)
 
	$daystokeep = GUICtrlRead($inpDaysToKeep)
 
	$scheduled = "0"
	If _IsChecked($inpScheduled) Then $scheduled = "1"
	$scheduletime = GUICtrlRead($inpScheduleTime)
 
	$debug = "0"
	If _IsChecked($inpDebug) Then $debug = "1"
 
	IniWrite($gInifile, "General", "server", $server)
	IniWrite($gInifile, "General", "user", $user)
	IniWrite($gInifile, "General", "password", $password)
	IniWrite($gInifile, "backup", "folder", $backupfolder)
	IniWrite($gInifile, "backup", "daystokeep", $daystokeep)
	IniWrite($gInifile, "schedule", "active", $scheduled)
	IniWrite($gInifile, "schedule", "time", $scheduletime)
	IniWrite($gInifile, "General", "debug", $debug)
EndFunc   ;==>SaveSettings
 
Func LoadSettings()
	$gServer = IniRead($gInifile, "General", "server", "")
	$gUser = IniRead($gInifile, "General", "user", "")
	$gPassword = IniRead($gInifile, "General", "password", "")
	$gDebug = IniRead($gInifile, "General", "debug", "")
 
	$gBackupFolder = IniRead($gInifile, "backup", "folder", "")
	$gDaysToKeep = IniRead($gInifile, "backup", "daystokeep", "")
 
	$gScheduled = IniRead($gInifile, "schedule", "active", "")
	$gScheduleTime = IniRead($gInifile, "schedule", "time", "")
 
	If $gDebug = "" Then $gDebug = "0"
	If $gDaysToKeep = "" Then $gDaysToKeep = "0"
	If $gScheduled = "" Then $gScheduled = "0"
	If $gScheduleTime = "" Then $gScheduleTime = "12.00.00"
 
	$gScheduleTime = StringReplace($gScheduleTime, ":", ".")
 
EndFunc   ;==>LoadSettings
 
Func _IsChecked($iControlID)
	Return BitAND(GUICtrlRead($iControlID), $GUI_CHECKED) = $GUI_CHECKED
EndFunc   ;==>_IsChecked
 
Func backupnow()
	AutoItSetOption("TrayIconHide", 0)
 
	$gServer = GUICtrlRead($inpServer)
	$gUser = GUICtrlRead($inpUser)
	$gPassword = GUICtrlRead($inpPassword)
	$gBackupFolder = GUICtrlRead($inpBackupFolder)
 
	$gDaysToKeep = "3"
	$gDebug = "0"
	If _IsChecked($inpDebug) Then $gDebug = "1"
 
	Call("backup")
 
	AutoItSetOption("TrayIconHide", 1)
EndFunc   ;==>backupnow
 
Func backup()
	$tt = _Now()
	$tt1 = StringMid($tt, 1, 2)
	$tt2 = StringMid($tt, 4, 2)
	$tt3 = StringMid($tt, 7, 4)
	$tt4 = StringMid($tt, 12, 2)
	$tt5 = StringMid($tt, 15, 2)
	$rtt = $tt3 & "_" & $tt2 & "_" & $tt1;&"_"&$tt4&"_"&$tt5
 
	$output = ''
 
	$conn = ObjCreate("ADODB.Connection")
	$DSN = "DRIVER={SQL Server};SERVER=" & $gServer & ";UID=" & $gUser & ";PWD=" & $gPassword & ";"
	$conn.Open($DSN)
	$rs = ObjCreate("ADODB.RecordSet")
	$rs.Open("SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');", $conn)
	If $rs.RecordCount Then
		If $gDebug = "1" Then TrayTip("SQLSupport", "Backup avviato", 1)
		DirCreate($gBackupFolder & "\" & $rtt)
		$rsdb = ObjCreate("ADODB.RecordSet")
		While Not $rs.EOF
			$dbname = $rs.Fields.Item("name").Value
			If $gDebug = "1" Then TrayTip("SQLSupport", "Backup database " & $dbname, 1)
			$rsdb.open("BACKUP DATABASE [" & $dbname & "] TO  DISK = N'" & $gBackupFolder & "\" & $rtt & "\" & $dbname & ".bak' WITH NOFORMAT, INIT,  NAME = N'" & $dbname & "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10", $conn)
			$rs.MoveNext
		WEnd
		$rsdb.close
		If $gDebug = "1" Then TrayTip("SQLSupport", "Backup completato con successo", 1)
		If Int($gDaysToKeep) > 0 Then
			If $gDebug = "1" Then TrayTip("SQLSupport", "Cancellazione vecchie copie", 1)
			Call("removefolder")
		EndIf
	Else
		If $gDebug = "1" Then TrayTip("SQLSupport", "Backup non eseguito: verificare i parametri", 5)
	EndIf
	$conn.close
EndFunc   ;==>backup
 
Func removefolder()
	$aRootFolders = _FileListToArrayEx($gBackupFolder, "\A[^\W]", 106) ; RegExp filter removes $RECYCLE.BIN, full path, no return count, folders only
	For $i = 0 To UBound($aRootFolders) - 1
		$aT = FileGetTime($aRootFolders[$i], 1) ; get creation time of each sub folder
		If Not @error Then $sCreationTime = $aT[0] & "/" & $aT[1] & "/" & $aT[2] & " " & $aT[3] & ":" & $aT[4] & ":" & $aT[5] ; convert time
		$iDifference = _DateDiff("D", $sCreationTime, $gCurrentTime) ; compare creation time with current time
		If $iDifference > Int($gDaysToKeep) Then ; if X days different
			DirRemove($aRootFolders[$i], 1) ; delete folder
		EndIf
	Next
EndFunc   ;==>removefolder
 
 
 
Func CheckConnection()
	$gServer = GUICtrlRead($inpServer)
	$gUser = GUICtrlRead($inpUser)
	$gPassword = _GUICtrlEdit_GetText($inpPassword)
 
	$conn = ObjCreate("ADODB.Connection")
	$DSN = "DRIVER={SQL Server};SERVER=" & $gServer & ";UID=" & $gUser & ";PWD=" & $gPassword & ";"
	$conn.Open($DSN)
	$rs = ObjCreate("ADODB.RecordSet")
	$rs.Open("SELECT name FROM master..sysdatabases ;", $conn)
	If $rs.RecordCount Then
		MsgBox(64, "SQLSupport :: Info", "Connessione verificata !")
	Else
		MsgBox(16, "SQLSupport :: Attenzione!!!", "Connessione non possibile: verificare i parametri !!")
	EndIf
	$conn.close
 
EndFunc   ;==>CheckConnection
 
Func restore()
 
	$sDrive = ""
	$sDir = ""
	$dbname = ""
	$sExt = ""
 
	$gServer = GUICtrlRead($inpServer)
	$gUser = GUICtrlRead($inpUser)
	$gPassword = _GUICtrlEdit_GetText($inpPassword)
 
	$gDebug = "0"
	If _IsChecked($inpDebug) Then $gDebug = "1"
 
	$sDatabase = 'master'
	$restorefolder = GUICtrlRead($inpRestoreFolder)
 
	If $gDebug = "1" Then TrayTip("SQLSupport", "Ripristino avviato", 1)
	$conn = ObjCreate("ADODB.Connection")
	$DSN = ("DRIVER={SQL Server};SERVER=" & $gServer & ";DATABASE=" & $sDatabase & ";UID=" & $gUser & ";PWD=" & $gPassword & ";")
	$conn.open($DSN)
	$rs = ObjCreate("ADODB.RecordSet")
 
	$aBackupList = _FileListToArrayEx($restorefolder, "*.bak", BitOR(1, 8, 32)) ; full path, no return count, files only
	For $i = 0 To UBound($aBackupList) - 1
		_PathSplit($aBackupList[$i], $sDrive, $sDir, $dbname, $sExt)
		;	  MsgBox(64,"SQLSupport :: Info", "RESTORE DATABASE ["&$dbname&"] FROM DISK = '"& $aBackupList[$i] &"' WITH REPLACE")
		$rs.open("RESTORE DATABASE [" & $dbname & "] FROM DISK = '" & $restorefolder[$i] & "' WITH REPLACE", $conn)
		If $gDebug = "1" Then TrayTip("SQLSupport", "Ripristinato database " & $dbname, 1)
	Next
	$rs.close
	$conn.close
	If $gDebug = "1" Then TrayTip("SQLSupport", "Ripristino completato", 1)
 
EndFunc   ;==>restore
 
 
Func autorun()
	If @OSARCH = "X64" Then
		$a = RegWrite("HKLM64\SOFTWARE\Microsoft\Windows\CurrentVersion\Run", "SQLSupport", "REG_SZ", '"' & @AutoItExe & '" /H')
	Else
		$a = RegWrite("HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run", "SQLSupport", "REG_SZ", '"' & @AutoItExe & '" /H')
	EndIf
 
	If $a = 1 Then
		MsgBox(64, "SQLSupport :: Info", "SQLSupport impostato per l'avvio automatico !")
	Else
		MsgBox(64, "SQLSupport :: Info", "Impostazione non riuscita !")
	EndIf
 
EndFunc
 
 
; #UDF# =======================================================================================================================
; Title .........: File List To Array
; AutoIt Version : 3.3.8.1
; Language ......: English
; Description ...: Lists files and\or folders in a specified path (Similar to using Dir with the /B Switch)
; Author(s) .....: DXRW4E
; Notes .........:
; ===============================================================================================================================
 
; #CURRENT# =====================================================================================================================
;~ _FileListToArrayEx
; ===============================================================================================================================
 
; #FUNCTION# =======================================================================================================================================================
; Name...........: _FileListToArrayEx
; Description ...: Lists files and\or folders in a specified path (Similar to using Dir with the /B Switch)
; Syntax.........: _FileListToArrayEx($sPath[, $sFilter = "*"[, $iFlag = 0]])
; Parameters ....: $sPath   - Path to generate filelist for.
;                  $sFilter - Optional the filter to use, default is *. (Multiple filter groups such as "All "*.png|*.jpg|*.bmp") Search the Autoit3 helpfile for the word "WildCards" For details.
;                  $iFlag   - Optional: specifies whether to return files folders or both Or Full Path (add the flags together for multiple operations):
;                  |$iFlag = 0 (Default) Return both files and folders
;                  |$iFlag = 1 Return files only
;                  |$iFlag = 2 Return Folders only
;                  |$iFlag = 4 Search SubDirectory
;                  |$iFlag = 8 Return Full Path
;                  |$iFlag = 16 $sFilter do Case-Sensitive matching (By Default $sFilter do Case-Insensitive matching)
;                  |$iFlag = 32 Disable the return the count in the first element - effectively makes the array 0-based (must use UBound() to get the size in this case).
;                    By Default the first element ($array[0]) contains the number of file found, the remaining elements ($array[1], $array[2], etc.)
;                  |$iFlag = 64 $sFilter is REGEXP Mod, See Pattern Parameters in StringRegExp (Can not be combined with flag 16)
;                  |$iFlag = 128 Return Backslash at the beginning of the file name, example Return "\Filename1.xxx" (Can not be combined with flag 8)
; Return values .: Failure - @Error
;                  |1 = Path not found or invalid
;                  |2 = Invalid $sFilter
;                  |3 = No File(s) Found
; Author ........: DXRW4E
; Modified.......:
; Remarks .......: The array returned is one-dimensional and is made up as follows:
;                                $array[0] = Number of Files\Folders returned
;                                $array[1] = 1st File\Folder
;                                $array[2] = 2nd File\Folder
;                                $array[3] = 3rd File\Folder
;                                $array[n] = nth File\Folder
; Related .......:
; Link ..........:
; Example .......: Yes
; Note ..........: Special Thanks to SolidSnake & Tlem
; ==================================================================================================================================================================
Func _FileListToArrayEx($sPath, $sFilter = "*", $iFlag = 0)
    $sPath = StringRegExpReplace($sPath & "\", "(?!\A)[\\/]+\h*", "\\")
    If Not FileExists($sPath) Then Return SetError(1, 1, "")
	If StringRegExp($sFilter, StringReplace('^\s*$|\v|[\\/:><"]|^\||\|\||\|$', "[" & Chr(BitAND($iFlag, 64) + 28) & '\/:><"]|^\||\|\||\|$', "\\\\")) Then Return SetError(2, 2, "")
	Local $hSearch, $sFile, $sFileList, $sSubDir = BitAND($iFlag, 4), $sDelim = "|", $sDirFilter = StringReplace($sFilter, "*", "")
    $hSearch = FileFindFirstFile($sPath & "*")
    If @Error Then Return SetError(3, 3, "")
	Local $hWSearch = $hSearch, $hWSTMP, $SearchWD, $Extended, $iFlags = StringReplace(BitAND($iFlag, 1) + BitAND($iFlag, 2), "3", "0")
	If BitAND($iFlag, 8) Then $sDelim &= $sPath
	If BitAND($iFlag, 128) Then $sDelim = "|\"
	If Not BitAND($iFlag, 64) Then $sFilter = StringRegExpReplace(BitAND($iFlag, 16) & "(?i)(", "16\(\?\i\)|\d+", "") & StringRegExpReplace(StringRegExpReplace(StringRegExpReplace(StringRegExpReplace($sFilter, "[^*?|]+", "\\Q$0\\E"), "\\E(?=\||$)", "$0\$"), "(?<=^|\|)\\Q", "^$0"), "\*+", ".*") & ")"
	While 1
        $sFile = FileFindNextFile($hWSearch)
        If @Error Then
            If $hWSearch = $hSearch Then ExitLoop
            FileClose($hWSearch)
            $hWSearch -= 1
            $SearchWD = StringLeft($SearchWD, StringInStr($SearchWD, "\", 1, -2))
        ElseIf $sSubDir Then
            $Extended = @Extended
            If ($iFlags + $Extended <> 2) Then
                If $sDirFilter Then
                    If StringRegExp($sFile, $sFilter) Then $sFileList &= $sDelim & $SearchWD & $sFile
                Else
                    $sFileList &= $sDelim & $SearchWD & $sFile
                EndIf
            EndIf
            If Not $Extended Then ContinueLoop
            $hWSTMP = FileFindFirstFile($sPath & $SearchWD & $sFile & "\*")
            If $hWSTMP = -1 Then ContinueLoop
            $hWSearch = $hWSTMP
            $SearchWD &= $sFile & "\"
        Else
            If ($iFlags + @Extended = 2) Or StringRegExp($sFile, $sFilter) = 0 Then ContinueLoop
            $sFileList &= $sDelim & $sFile
        EndIf
    WEnd
    FileClose($hSearch)
    If Not $sFileList Then Return SetError(3, 3, "")
	Return StringSplit(StringTrimLeft($sFileList, 1), "|", StringReplace(BitAND($iFlag, 32), "32", 2))
EndFunc	;==>_FileListToArrayEx
autoit/sqlsupport.txt · Ultima modifica: 01/03/2014 14:35 da luigi

Strumenti Pagina