# Export-MSSQLServerObjects # ---------------------------------------------------------------------------- # This script exports MSSQL Server Procedures, Functions and Views to the Filesystem # # Returns: - # ---------------------------------------------------------------------------- # Copyright (c) 2025 by Digital Data GmbH # # Digital Data GmbH • Ludwig-Rinn-Strasse 16 • D-35452 Heuchelheim # Tel.: 0641/202360 • E-Mail: info-flow@digitaldata.works # ---------------------------------------------------------------------------- # Creation Date / Author: 24.09.2025 / MK # Version Date / Editor: 24.09.2025 / MK # Version Number: 1.0.0.0 #-----------------------------------------------------------------------------------------------------# ######################################## check for arguments ########################################## #-----------------------------------------------------------------------------------------------------# #-----------------------------------------------------------------------------------------------------# ################################## add additional buildin assemblys ################################### #-----------------------------------------------------------------------------------------------------# #Add-Type -AssemblyName System.Windows.Forms -ErrorAction Stop #Add-Type -AssemblyName PresentationCore -ErrorAction Stop #Add-Type -AssemblyName PresentationFramework -ErrorAction Stop #-----------------------------------------------------------------------------------------------------# ############################################ set variables ############################################ #-----------------------------------------------------------------------------------------------------# #-----------------------------------------------------------------------------------------------------# ############################################ set variables ############################################ #-----------------------------------------------------------------------------------------------------# Set-Variable -Scope Global -Name ScriptName -Value (($MyInvocation.MyCommand.Name) -split "\.")[0].ToString() Set-Variable -Scope Global -Name ScriptPath -Value (Split-Path ($MyInvocation.MyCommand.Path)) Set-Variable -Scope Global -Name ConfigFile -Value (Get-ChildItem -Path "$ScriptPath" -Recurse:$false -Filter "$ScriptName`_Settings.ini" -File -Force).FullName | Select-Object -First 1 Set-Variable -Scope Global -Name ConfigFileContent -Value $NULL Set-Variable -Scope Global -Name Timestamp1 -Value $(Get-Date -Format 'ddMMyyyy') Set-Variable -Scope Global -Name Timestamp2 -Value $(Get-Date -Format 'ddMMyyyy_HHmmss') Set-Variable -Scope Global -Name Timestamp3 -Value $(Get-Date -Format 'ddMMyyyy_HHmmssffff') Set-Variable -Scope Global -Name Timestamp4 -Value $(Get-Date -Format 'yyyyMMdd HH:mm:ss.fff') Set-Variable -Scope Global -Name Item -Value $NULL Set-Variable -Scope Global -Name Items -Value $NULL Set-Variable -Scope Global -Name VersionSeperator -Value "~" Set-Variable -Scope Global -Name Counter1 -Value 0 Set-Variable -Scope Global -Name LogFile -Value "$ScriptName`_$Timestamp2.log" Set-Variable -Scope Global -Name LogFileKeepTime -Value 60 Set-Variable -Scope Global -Name LogPath -Value $NULL Set-Variable -Scope Global -Name FileDelayAge -Value 5 Set-Variable -Scope Global -Name FileTest -Value $NULL Set-Variable -Scope Global -Name PathTest -Value $NULL Set-Variable -Scope Global -Name Module -Value $NULL Set-Variable -Scope Global -Name Modules -Value ("Read-ConfigFile2","Write-LogFile","Remove-Item-withLogging","Update-ReplacePlaceholder-withLogging", "Remove-SpecialCharacter-withLogging","Test-Path-withLogging") Set-Variable -Scope Global -Name ModuleOverrideSourcePath -Value $NULL Set-Variable -Scope Global -Name ModuleDefaultSourcePath -Value "P:\Skriptentwickung\current\Modules" Set-Variable -Scope Global -Name ModuleHKLMRegistryPath -Value "HKLM:\SOFTWARE\Digital Data\Modules" Set-Variable -Scope Global -Name ModuleHKCURegistryPath -Value "HKCU:\SOFTWARE\Digital Data\Modules" #Set-Variable -Scope Global -Name UninstallServiceProfile -Value $NULL #Set-Variable -Scope Global -Name InstallServiceProfile -Value $NULL #-----------------------------------------------------------------------------------------------------# ############################################ set functions ############################################ #-----------------------------------------------------------------------------------------------------# Function Import-CustomModule { <# .SYNOPSIS Function will load external - additional - PowerShell Modules into current PSSession. .DESCRIPTION By working with Modules, this Function is necessary to load external Modul Functions into the current PowerShell Session. In a productive Enviroment it is recommanded to let this Function set the Registry Key in HKLM for the ModuleSourcePath. In develepment and Test Enviroment it is possible, to work with distributed Folders with different Modules. Therefor the Parameter "ModuleOverrideSourcePath" and the preset Variable "ModuleDefaultSourcePath" are made for. After a successful Import of a Module, Function will Return $True, otherwise a $False. .REQUIREMENT General PowerShell V3 .REQUIREMENT Assembly System.Windows.Forms, PresentationCore, PresentationFramework .REQUIREMENT Variables ModuleOverrideSourcePath, ModuleName, Path, Paths, PathTest, FileTest, Result .REQUIREMENT Variables preSet ScriptName, ScriptPath, ModuleDefaultSourcePath (optional) .REQUIREMENT Functions .VERSION 1.2.0.0 / 09.11.2024 .PARAMETER ModuleName Give the Module Name, you want to load into the current PSSession (without File-Extension). .PARAMETER ModuleOverrideSourcePath Optional Parameter. By giving the ModuleOverrideSourcePath, Function will not check other Paths for the Function you want to load. .PARAMETER ModuleFileExtensions Optional Parameter. Give the Module File-Extension (regular: "psm1" or "dll") without a dot ("."), this is just for the checking routine, not the Import itself.')] .PARAMETER Force Optional Parameter. By using the Force Parameter, Module will be unload and reload. .EXAMPLE Import-CustomModule -ModuleName Write-LogFile -ModuleFileExtensions psm1 .EXAMPLE Import-CustomModule -ModuleName Write-LogFile -Force .EXAMPLE Import-CustomModule -ModuleName Write-LogFile -ModuleFileExtensions psm1 -ModuleOverrideSourcePath D:\ScriptFiles\Modules #> Param ( [Parameter(Position=0,Mandatory=$True,ValueFromPipeline=$True,HelpMessage='Give the ModuleName, you want to load into the current PSSession (without File-Extension)')] [ValidateNotNullOrEmpty()] [String]$ModuleName, [Parameter(Position=1,Mandatory=$False,HelpMessage='Optional Parameter. By giving the ModuleOverrideSourcePath, Function will not check other Paths for the Function you want to load.')] [ValidateNotNullOrEmpty()] [String]$ModuleOverrideSourcePath, [Parameter(Position=2,Mandatory=$False,HelpMessage='Optional Parameter. Give the Module File-Extension (regular: "psm1" or "dll") without a dot ("."), this is just for the checking routine, not the Import itself.')] [ValidateSet("psm1","dll")] [Array]$ModuleFileExtensions = @("psm1","dll"), [Parameter(Position=3,Mandatory=$False,HelpMessage='Optional Parameter. By using the Force Parameter, Module will be unload and reload.')] [Switch]$Force ) #end param Process { #Clear Error Variable $error.clear() #Loop for every possible File Extension (eg. psm1 and dll) FOREACH ($ModuleFileExtension in $ModuleFileExtensions) { #If FileExtension was given, remove it! Because otherwise "Import-Module" Function will have trouble importing. $ModuleName = $ModuleName -Replace("\.(\w{3}|\w{4})$","") Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: You want to load Module $ModuleName.$ModuleFileExtension" IF ((([String]::IsNullOrWhiteSpace($ScriptName) -ne $True) -and ([String]::IsNullOrEmpty($ScriptName) -ne $True)) -and (([String]::IsNullOrWhiteSpace($ScriptPath) -ne $True) -and ([String]::IsNullOrEmpty($ScriptPath) -ne $True))) { #Try this if $ModuleOverrideSourcePath was given by calling the function IF ((([String]::IsNullOrWhiteSpace($ModuleOverrideSourcePath)) -ne $True) -and (([String]::IsNullOrEmpty($ModuleOverrideSourcePath)) -ne $True)) { Write-Host "DEBUG Info - Import-CustomModule: Function has been called with 'ModuleOverrideSourcePath' Parameter input!" Write-Host "DEBUG Info - Import-CustomModule: Testing for existence: $ModuleOverrideSourcePath\$ModuleName.$ModuleFileExtension" $FileTest = Test-Path -Path "$ModuleOverrideSourcePath\$ModuleName.$ModuleFileExtension" -PathType Leaf IF ($FileTest -eq $True) { Write-Host "DEBUG Info - Import-CustomModule: $ModuleOverrideSourcePath and ModuleName seems to exist." Write-Host "DEBUG Info - Import-CustomModule: Trying to import Module: $ModuleName.$ModuleFileExtension" Try { $Result = Import-Module $ModuleOverrideSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue IF ("$Result" -eq "$ModuleName") { Write-Host "DEBUG Info - Import-CustomModule: Successfully loaded Module: $ModuleName.$ModuleFileExtension" Return $True } ELSE { Write-Error "DEBUG Info - Import-CustomModule: Unsuccessfully loaded Module: $ModuleName.$ModuleFileExtension" Return $False } #end if/else } Catch { Write-Host "DEBUG Info - Import-CustomModule: Error while importing the Module:" Write-Host "DEBUG Info - Import-CustomModule: $ModuleName" Write-Host $Error Return $False } #end try/catch } ELSE { Write-Host "DEBUG Info - Import-CustomModule: ModuleOverrideSourcePath and/or ModuleName seems not to exist." Write-Host "DEBUG Info - Import-CustomModule: Cannot load Module, please check your input!" Return $False } #end if/else } ELSE { #If $ModuleOverrideSourcePath was not given, try to find a matching folder Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Function has been called without 'ModuleOverrideSourcePath' Parameter input!" Write-Host "DEBUG Info - Import-CustomModule: Trying to find Module Files on some Places of this Computer." #Set dynamic Array for locations Modul Path could be, even for some testing. #The first value of the array is just a dummy and will never be used -but keep it for the array value starting with 0! [System.Collections.ArrayList]$Paths = @() Write-Host "" $Paths.Add("$env:systemroot\") | Out-Null IF (([String]::IsNullOrEmpty($ModuleDefaultSourcePath)) -or ([String]::IsNullOrWhiteSpace($ModuleDefaultSourcePath))) { Write-Host "DEBUG Info - Import-CustomModule: ModuleDefaultSourcePath was not set! That could be a normal behavior in productive enviroment!" } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Possible Path (1): $ModuleDefaultSourcePath" -ErrorAction SilentlyContinue $Paths.Add("$ModuleDefaultSourcePath") | Out-Null } #end if/else IF ([String]::IsNullOrEmpty($ScriptPath) -or ([String]::IsNullOrWhiteSpace($ScriptPath))) { Write-Host "DEBUG Info - Import-CustomModule: ScriptPath is invalid! That is terrifying! How could that be???" } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Possible Path (2): $ScriptPath" -ErrorAction SilentlyContinue $Paths.Add("$ScriptPath") | Out-Null Write-Host "DEBUG Info - Import-CustomModule: Possible Path (3): $($ScriptPath+"\Module")" -ErrorAction SilentlyContinue $Paths.Add("$($ScriptPath+"\Module")") | Out-Null Write-Host "DEBUG Info - Import-CustomModule: Possible Path (4): $($ScriptPath+"\Modules")" -ErrorAction SilentlyContinue $Paths.Add("$($ScriptPath+"\Modules")") | Out-Null } #end if/else IF (([String]::IsNullOrEmpty((Get-Item $ScriptPath).Parent.FullName)) -or ([String]::IsNullOrWhiteSpace((Get-Item $ScriptPath).Parent.FullName))) { Write-Host "DEBUG Info - Import-CustomModule: ScriptPath has no Parent Folders!" } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Possible Path (5): $((Get-Item $ScriptPath).Parent.FullName)" -ErrorAction SilentlyContinue $Paths.Add("$((Get-Item $ScriptPath).Parent.FullName)") | Out-Null Write-Host "DEBUG Info - Import-CustomModule: Possible Path (6): $(((Get-Item $ScriptPath).Parent.FullName)+"\Module")" -ErrorAction SilentlyContinue $Paths.Add("$(((Get-Item $ScriptPath).Parent.FullName)+"\Module")") | Out-Null Write-Host "DEBUG Info - Import-CustomModule: Possible Path (7): $(((Get-Item $ScriptPath).Parent.FullName)+"\Modules")" -ErrorAction SilentlyContinue $Paths.Add("$(((Get-Item $ScriptPath).Parent.FullName)+"\Modules")") | Out-Null } #end if/else IF (([String]::IsNullOrEmpty($((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath))) -or ([String]::IsNullOrWhiteSpace($((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)))) { Write-Host "DEBUG Info - Import-CustomModule: ModuleSourcePath was not set to Windows Registry (HKLM)!" } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Possible Path (8): $((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)" -ErrorAction SilentlyContinue $Paths.Add("$((Get-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)") | Out-Null } #end if/else IF (([String]::IsNullOrEmpty($((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath))) -or ([String]::IsNullOrWhiteSpace($((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)))) { Write-Host "DEBUG Info - Import-CustomModule: ModuleSourcePath was not set to Windows Registry (HKCU)!" } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Possible Path (8): $((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)" -ErrorAction SilentlyContinue $Paths.Add("$((Get-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -ErrorAction SilentlyContinue).ModuleSourcePath)") | Out-Null } #end if/else [Int]$Counter = 0 [String]$ModuleSourcePath = $Null #Loop for multiple Pathtests - for each Path, where Module files could be DO { $Counter++ | Out-Null Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Testing mutiple Paths ( $Counter of"($($Paths.Count)-1)") for existence, now testing:" Write-Host "DEBUG Info - Import-CustomModule: $($Paths[$Counter])" IF ((([String]::IsNullOrWhiteSpace($($Paths[$Counter]))) -ne $True) -and (([String]::IsNullOrEmpty($($Paths[$Counter])) -ne $True))) { $PathTest = (Test-Path $($Paths[$Counter]) -ErrorAction SilentlyContinue) IF ($PathTest -eq $True) { Write-Host "DEBUG Info - Import-CustomModule: Yes, Path seems to exist." Write-Host "DEBUG Info - Import-CustomModule: Lets check, if there are any Module Files, in it." $FileTest = Get-ChildItem -Path $($Paths[$Counter]) -Filter *.$ModuleFileExtension IF ($($FileTest.count) -gt 0) { Write-Host "DEBUG Info - Import-CustomModule: Found $($FileTest.count) $ModuleFileExtension Module Files in Path!" Set-Variable -Name ModuleSourcePath -Value $($Paths[$Counter]) -Scope local } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Found no $ModuleFileExtension Module Files in Path!" } #end if/else } ELSE { Write-Host "DEBUG Info - Import-CustomModule: No, Path seems not to exist." } #end if/else } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Path seems to be invalid!" } #end if/else } #end do UNTIL ($Counter -ge ($($Paths.Count)-1) -or ($ModuleSourcePath -eq $($Paths[$Counter]))) IF ($ModuleSourcePath -eq $($Paths[$Counter])) { $FileTest = (Test-Path -Path $ModuleSourcePath\$ModuleName.$ModuleFileExtension -PathType Leaf -ErrorAction SilentlyContinue) IF ($FileTest -eq $True) { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Trying to import Module: $ModuleName.$ModuleFileExtension" Try { $Result = Import-Module $ModuleSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue IF ("$Result" -eq "$ModuleName") { Write-Host "DEBUG Info - Import-CustomModule: Successfully loaded Module: $ModuleName.$ModuleFileExtension" Set-Variable -Name ModuleDefaultSourcePath -Value $ModuleSourcePath -Scope Global Return $True } ELSE { Write-Error "DEBUG Info - Import-CustomModule: Unsuccessfully loaded Module: $ModuleName.$ModuleFileExtension" Return $False } #end if/else } Catch { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Loading Module: $ModuleName went wrong." Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this error!" Write-Host $Error Return $False exit } #end try/catch } ELSE { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Module does not exist!" Write-Host "DEBUG Info - Import-CustomModule: Skipping: $ModuleName.$ModuleFileExtension" } #end if/else } ELSE { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Cant locate Module Files automaticlly!" Write-Host "DEBUG Info - Import-CustomModule: Please select Folder in Dialog." #Prepare Folder Browser Dialog, to choose the Directory with the .psm1 Files. $FolderBrowserDialog = New-Object System.Windows.Forms.FolderBrowserDialog $FolderBrowserDialog.Rootfolder = "Desktop" $FolderBrowserDialog.Description = "Please, choose the Folder, where the Module ""$ModuleName.$ModuleFileExtension"" is stored." $FolderBrowserDialog.SelectedPath = "$ScriptPath" $FolderBrowserDialog.ShowNewFolderButton = $True DO { #Now show the Folder Browser, if neccessary in a loop $FolderBrowserDialogShow = $FolderBrowserDialog.ShowDialog() #By pressing the OK Button.. If ($FolderBrowserDialogShow -eq "OK") { #Save selected folder path in the variable $ModuleSourcePath = ($FolderBrowserDialog.SelectedPath) Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: You choose: $ModuleSourcePath" Write-Host "DEBUG Info - Import-CustomModule: ...testing, if Module ""$ModuleName.$ModuleFileExtension"" can be found there." $FileTest = (Test-Path $ModuleSourcePath\$ModuleName.$ModuleFileExtension -PathType Leaf -ErrorAction SilentlyContinue) IF ($FileTest -eq $True) { Write-Host "DEBUG Info - Import-CustomModule: Module seems to exist, in the selected Folder." Write-Host "DEBUG Info - Import-CustomModule: Now trying to load Module: $ModuleName.$ModuleFileExtension" Try { $Result = Import-Module $ModuleSourcePath\$ModuleName -Verbose -DisableNameChecking -Scope Global -Force:$Force -PassThru -ErrorAction SilentlyContinue IF ("$Result" -eq "$ModuleName") { Set-Variable -Name ModuleDefaultSourcePath -Value $ModuleSourcePath -Scope Global $MessageBoxBody = "Module: $ModuleName.$ModuleFileExtension - successsfully loaded into current PSSession!" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Information" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null #Set new default path for next foreach loop Set-Variable -Name ModuleDefaultSourcePath -Value $ModuleSourcePath -Scope Global Set-Variable -Name ModuleOverrideSourcePath -Value $ModuleSourcePath -Scope Global Set-Variable -Name ModuleSourcePath -Value $ModuleSourcePath -Scope Global } ELSE { $MessageBoxBody = "Module: $ModuleName.$ModuleFileExtension - cannot load into current PSSession!" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Warning" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null } #end If/else } Catch { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Loading Module: $ModuleName.$ModuleFileExtension went wrong." Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this error!" Write-Host $Error exit } #end try/catch } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Module seems not to exist, in the selected Folder." Write-Host "DEBUG Info - Import-CustomModule: Please, select another one! ...this Time the right!" $MessageBoxBody = "Module seems not to exist, in the selected Folder! Please, select another one!" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Warning" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) | Out-Null } #end if/else } ELSE { #If you didnt pressed the OK Button.. Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Operation cancelled by user." Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this!" exit } #end if/else } #end do #Variable "$?" is $True when last operation was ok UNTIL ((($FileTest -eq $True) -and ($? -eq $True)) -or ($FolderBrowserDialogShow -eq "Cancel")) IF (([String]::IsNullOrWhiteSpace($ModuleSourcePath) -ne $True) -and ([String]::IsNullOrEmpty($ModuleSourcePath) -ne $True)) { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Should ModuleSourcePath written to Windows Registry?" $MessageBoxBody = "Would you like to save the ModulePath to the Windows Registry?" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "YesNo" $MessageBoxIcon = "Question" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) IF ($MessageBox -eq 'Yes') { $PathTest = (Test-Path -Path "$ModuleHKLMRegistryPath" -ErrorAction SilentlyContinue) $MessageBox = $NULL IF ($PathTest -eq $False) { Write-Host "DEBUG Info - Import-CustomModule: Registry Key seems not to exist." Write-Host "DEBUG Info - Import-CustomModule: Trying to write ModuleSourcepath to HKLM." Try { New-Item -Path "$ModuleHKLMRegistryPath" -Force -ErrorAction Stop | Out-Null New-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null } Catch { Write-Host $Error $MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Check your access rights! To bypass this issue you can write the ModuleSourcePath to User Registry (HKCU). Would you?" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "YesNo" $MessageBoxIcon = "Question" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) } #end try/catch } ELSEIF ($PathTest -eq $True) { Write-Host "DEBUG Info - Import-CustomModule: Registry Key seems to exist." Write-Host "DEBUG Info - Import-CustomModule: Trying to write ModuleSourcepath to HKLM." Try { Set-ItemProperty -Path "$ModuleHKLMRegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null } Catch { Write-Host $Error $MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Check your access rights! To Bypass Error: Write ModuleSource Path to User Registry (HKCU)?" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "YesNo" $MessageBoxIcon = "Question" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) } #end try/catch } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Something went wrong, by getting the ModuleSourcePath!" Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this!" exit } #end if/elseif/else #Block for trying to write the ModuleSourcePath to the User Registry, if System Registry failed. IF ($MessageBox -eq 'Yes') { $PathTest = (Test-Path -Path "$ModuleHKCURegistryPath" -ErrorAction SilentlyContinue) $MessageBox = $NULL IF ($PathTest -eq $False) { Write-Host "DEBUG Info - Import-CustomModule: Registry Key seems not to exist." Write-Host "DEBUG Info - Import-CustomModule: Trying to write ModuleSourcepath to HKCU." Try { New-Item -Path "$ModuleHKCURegistryPath" -Force -ErrorAction Stop | Out-Null New-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null } Catch { Write-Host $Error $MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Not even to User Registry! Check your access rights! Exiting now.." $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Warning" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) exit } #end try/catch } ELSEIF ($PathTest -eq $True) { Write-Host "DEBUG Info - Import-CustomModule: Registry Key seems to exist." Write-Host "DEBUG Info - Import-CustomModule: Trying to write ModuleSourcepath to HKCU." Try { Set-ItemProperty -Path "$ModuleHKCURegistryPath" -Name ModuleSourcePath -Value $ModuleSourcePath -ErrorAction Stop | Out-Null } Catch { Write-Host $Error $MessageBoxBody = "Could not save ModuleSourcePath to Windows Registry! Not even to User Registry! Check your access rights! Exiting now.." $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Warning" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) exit } #end try/catch } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Something went wrong, by getting the ModuleSourcePath!" Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this!" exit } #end if/elseif/else } #end if } ELSE { Write-Host "DEBUG Info - Import-CustomModule: You choose, not to save the ModuleSourcePath to the Windows Registry!" $MessageBoxBody = "You choose, not to save the ModuleSourcePath to the Windows Registry!" $MessageBoxTitle = "ScriptName: $ScriptName - Module/Section: Import-CustomModule" $MessageBoxButtonType = "OK" $MessageBoxIcon = "Warning" $MessageBox = [Windows.Forms.MessageBox]::Show($MessageBoxBody,$MessageBoxTitle,$MessageBoxButtonType,$MessageBoxIcon) } #end if/else } ELSE { Write-Host "DEBUG Info - Import-CustomModule: Something went wrong, by getting the ModuleSourcePath!" Write-Host "DEBUG Info - Import-CustomModule: Exiting Script, because of this!" exit } #end if/else } #end if/else } #end if/ else } ELSE { Write-Host "" Write-Host "DEBUG Info - Import-CustomModule: Required Variables (ScriptName and ScriptPath) were not set!" Write-Host "DEBUG Info - Import-CustomModule: Module Import is unvailable without this Variables!" } #end if/else } #end foreach } #end process } #end function # Funktion: Prüfen und ggf. installieren/laden des SqlServer-Moduls function Test-SqlServerModule { try { if (-not (Get-Module -ListAvailable -Name SqlServer)) { Write-Logfile -LogLine "SqlServer-Modul not found. Try to install..." Install-Module -Name SqlServer -Scope CurrentUser -AllowClobber -Force -ErrorAction Stop } Import-Module SqlServer -ErrorAction Stop return $true } catch { Write-Logfile -LogLine "Cannot load or install the SqlServer module: $($_.Exception.Message)" return $false } } # Funktion: Prüfen/Anlegen des Output-Ordners function Test-OutputFolder { try { if (-not (Test-Path $outputFolder)) { Write-Logfile -LogLine "Target folder does not exist, try to create: $outputFolder" New-Item -ItemType Directory -Path $outputFolder -Force | Out-Null } return (Test-Path $outputFolder) } catch { Write-Logfile -LogLine "Cannot create target folder: $($_.Exception.Message)" return $false } } # Funktion um ungültige Zeichen zu entfernen function Get-SafeFileName { param( [Parameter(Mandatory=$true)] [string]$InputString, [string]$Replacement = "_" # Standard: verbotene Zeichen werden entfernt ) # Liste der verbotenen Zeichen $invalidChars = [System.IO.Path]::GetInvalidFileNameChars() # Regex-Muster aus den ungültigen Zeichen bauen $pattern = ($invalidChars | ForEach-Object { [Regex]::Escape($_) }) -join "|" # Ersetzen return ($InputString -replace $pattern, $Replacement) } # Hilfsfunktion zum Export function Export-Objects($objects, $subfolder, $ExportWithDBSchemaName) { $folder = Join-Path $outputFolder $subfolder if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } foreach ($object in $objects) { if (-not $object.IsSystemObject) { IF ($ExportWithDBSchemaName) { $fileName = "$($object.Schema).$($object.Name).sql" } Else { $fileName = "$($object.Name).sql" } $fileName = Get-SafeFileName -InputString $fileName $filePath = Join-Path $folder $fileName $scripter.Options.FileName = $filePath $scripter.Script($object) Write-Logfile -LogLine "Exporting $($subfolder): $filePath" } } } # Export Tabellen inkl. Constraints und Indizes function Export-Tables($db, $subfolder, $ExportWithDBSchemaName) { $folder = Join-Path $outputFolder $subfolder if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } foreach ($table in $db.Tables) { if (-not $table.IsSystemObject) { IF ($ExportWithDBSchemaName) { $fileName = "$($table.Schema).$($table.Name).sql" } Else { $fileName = "$($table.Name).sql" } $fileName = Get-SafeFileName -InputString $fileName $filePath = Join-Path $folder $fileName # Optionen für Tabellen-Export inkl. Constraints/Indizes $scripter.Options.FileName = $filePath $scripter.Options.DriAllConstraints = $true # alle Constraints $scripter.Options.DriAllKeys = $true # Primary/Foreign Keys $scripter.Options.DriIndexes = $true # Indizes $scripter.Options.ClusteredIndexes = $true $scripter.Options.NonClusteredIndexes = $true $scripter.Script($table) Write-Logfile -LogLine "Exporting table: $filePath" } } } # Export DDL-Trigger (auf Datenbankebene) function Export-DatabaseTriggers($db, $subfolder, $ExportWithDBSchemaName) { $folder = Join-Path $outputFolder "$subfolder\Database" if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } foreach ($trig in $db.Triggers) { if (-not $trig.IsSystemObject) { $fileName = "DBTrigger.$($trig.Name).sql" $fileName = Get-SafeFileName -InputString $fileName $filePath = Join-Path $folder $fileName $scripter.Options.FileName = $filePath $scripter.Script($trig) Write-Logfile -LogLine "Exporting DB-Trigger: $filePath" } } } # Export DML-Trigger (Tabellen-Trigger) function Export-TableTriggers($db, $subfolder, $ExportWithDBSchemaName) { $folder = Join-Path $outputFolder "$subfolder\Table" if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } foreach ($table in $db.Tables) { if (-not $table.IsSystemObject) { foreach ($trig in $table.Triggers) { $folder = Join-Path $outputFolder "$subfolder\Table\$table" if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder -ErrorAction SilentlyContinue | Out-Null } if (-not $trig.IsSystemObject) { IF ($ExportWithDBSchemaName) { $fileName = "$($table.Schema).$($table.Name).$($trig.Name).sql" } Else { $fileName = "$($table.Name).$($trig.Name).sql" } $fileName = Get-SafeFileName -InputString $fileName $filePath = Join-Path $folder $fileName $scripter.Options.FileName = $filePath $scripter.Script($trig) Write-Logfile -LogLine "Exporting table Trigger: $filePath" } } } } } # Export Linked Servers function Export-LinkedServers($server, $subfolder, $ExportWithDBSchemaName) { $folder = Join-Path $outputFolder $subfolder if (-not (Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } foreach ($ls in $server.LinkedServers) { # System-Linked-Server wie "LOCALSERVER" überspringen if ($ls.Name -ne "LOCALSERVER") { $fileName = "$($ls.Name).sql" $fileName = Get-SafeFileName -InputString $fileName $filePath = Join-Path $folder $fileName $scripter.Options.FileName = $filePath $scripter.Script($ls) Write-Logfile -LogLine "Exporting Linked Server: $filePath" } } } #-----------------------------------------------------------------------------------------------------# ########################################### preparing part ############################################ #-----------------------------------------------------------------------------------------------------# #Clear Console Content Clear-Host #Load external Modules - use Force ( -Force) Parameter, to reload in every run FOREACH ($Module in $Modules) { $Result = Import-CustomModule -ModuleName $Module -Force IF ($Result -eq $False) { Write-Host "DEBUG Info: Module: $Module was not successful been loaded!" Write-Host "DEBUG Info: Please load the Module and try again, running this Function/Module!" Write-Host "DEBUG Info: Exiting, because of this Issue." Write-Host $Error EXIT } #end if } #end foreach #Read ConfigFile and load its content into a Object [OBJECT]$ConfigFileContent = Read-ConfigFile2 -ConfigFile $ConfigFile #Allocate Variable Values, depending on the read ConfigFile [ARRAY]$LogPaths = $ConfigFileContent."LogPath_$($ConfigFileContent.LogPath[0])" [STRING]$LogFileKeepTime = $ConfigFileContent."LogFileKeepTime_$($ConfigFileContent.LogFileKeepTime[0])" [STRING]$ServerName = $ConfigFileContent."ServerName_$($ConfigFileContent.ServerName[0])" [STRING]$DatabaseName = $ConfigFileContent."DatabaseName_$($ConfigFileContent.DatabaseName[0])" [BOOL]$ExportWithDBSchemaName = [System.Convert]::ToBoolean(($ConfigFileContent."ExportWithDBSchemaName_1")[0]) [BOOL]$ExportStoredProcedures = [System.Convert]::ToBoolean(($ConfigFileContent."ExportStoredProcedures_1")[0]) [BOOL]$ExportViews = [System.Convert]::ToBoolean(($ConfigFileContent."ExportViews_1")[0]) [BOOL]$ExportUserDefinedFunctions = [System.Convert]::ToBoolean(($ConfigFileContent."ExportUserDefinedFunctions_1")[0]) [BOOL]$ExportTables = [System.Convert]::ToBoolean(($ConfigFileContent."ExportTables_1")[0]) [BOOL]$ExportTriggers = [System.Convert]::ToBoolean(($ConfigFileContent."ExportTriggers_1")[0]) [BOOL]$ExportLinkedServers = [System.Convert]::ToBoolean(($ConfigFileContent."ExportLinkedServers_1")[0]) [String]$outputFolder = Join-Path -Path $env:USERPROFILE -ChildPath "\Desktop\SQLExport\$databaseName" # Zielordner für die .sql-Dateien #-----------------------------------------------------------------------------------------------------# ############################################# main part ############################################### #-----------------------------------------------------------------------------------------------------# #Clear Error Variable $Error.Clear() | Out-Null #Clear Console Output Clear-Host Write-Logfile -LogLine " " Write-Logfile -LogLine "********************************************************************************" Write-Logfile -LogLine "Program Startup: $ScriptName on $env:COMPUTERNAME," Write-Logfile -LogLine "from Account $env:USERDOMAIN\$env:USERNAME." Write-Logfile -LogLine "********************************************************************************" if (Test-SqlServerModule -and Test-OutputFolder) { Try { Write-Logfile -LogLine "Connectiong to Server: $serverName" $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName Write-Logfile -LogLine "Connectiong to Database $db" $db = $server.Databases[$databaseName] # Scripter konfigurieren $scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter ($server) $scripter.Options.ScriptDrops = $false $scripter.Options.WithDependencies = $false $scripter.Options.IncludeHeaders = $true $scripter.Options.SchemaQualify = $true $scripter.Options.SchemaQualifyForeignKeysReferences = $true $scripter.Options.ToFileOnly = $true $scripter.Options.Encoding = [System.Text.Encoding]::UTF8 $scripter.Options.AppendToFile = $false Write-Logfile -LogLine " " Write-Logfile -LogLine "Starting export..." # Export durchführen IF ($ExportStoredProcedures) {Export-Objects $db.StoredProcedures "Procedures" $ExportWithDBSchemaName} IF ($ExportViews) {Export-Objects $db.Views "Views" $ExportWithDBSchemaName} IF ($ExportUserDefinedFunctions) {Export-Objects $db.UserDefinedFunctions "Functions" $ExportWithDBSchemaName} IF ($ExportTables) {Export-Tables $db "Tables" $ExportWithDBSchemaName} IF ($ExportTriggers) {Export-DatabaseTriggers $db "Triggers" $ExportWithDBSchemaName} IF ($ExportTriggers) {Export-TableTriggers $db "Triggers" $ExportWithDBSchemaName} IF ($ExportLinkedServers) {Export-LinkedServers $server "LinkedServers" $ExportWithDBSchemaName} Write-Logfile -LogLine " " Write-Logfile -LogLine "Export complete. Files in $outputFolder" Start-Process $outputFolder } catch { Write-Logfile -LogLine "Error while exporting: $($_.Exception.Message)" } } else { Write-Logfile -LogLine "Abort SqlServer-Modul not installed or target path does not exist!" } Write-Logfile -LogLine " " Write-Logfile -LogLine "--------------------------------------------------------------------------------" Write-Logfile -LogLine "Checking for old LogFiles." Write-Logfile -LogLine "--------------------------------------------------------------------------------" Remove-Item-withLogging -Path $LogPath -FileKeepTime $LogFileKeepTime -FileBaseName $ScriptName Write-Logfile -LogLine " " Write-Logfile -LogLine "********************************************************************************" Write-Logfile -LogLine "Program Completed: $ScriptName on $env:COMPUTERNAME," Write-Logfile -LogLine "from Account $env:USERDOMAIN\$env:USERNAME." Write-Logfile -LogLine "********************************************************************************" #-----------------------------------------------------------------------------------------------------# ########################################### finishing part ############################################ #-----------------------------------------------------------------------------------------------------# #Enable only for debugging #exit Remove-Variable -Name ScriptName -Force -ErrorAction SilentlyContinue Remove-Variable -Name ScriptPath -Force -ErrorAction SilentlyContinue Remove-Variable -Name ConfigFile -Force -ErrorAction SilentlyContinue Remove-Variable -Name ConfigFileContent -Force -ErrorAction SilentlyContinue Remove-Variable -Name Timestamp1 -Force -ErrorAction SilentlyContinue Remove-Variable -Name Timestamp2 -Force -ErrorAction SilentlyContinue Remove-Variable -Name Timestamp3 -Force -ErrorAction SilentlyContinue Remove-Variable -Name Timestamp4 -Force -ErrorAction SilentlyContinue Remove-Variable -Name Item -Force -ErrorAction SilentlyContinue Remove-Variable -Name Items -Force -ErrorAction SilentlyContinue Remove-Variable -Name VersionSeperator -Force -ErrorAction SilentlyContinue Remove-Variable -Name FileDelayAge -Force -ErrorAction SilentlyContinue Remove-Variable -Name FileTest -Force -ErrorAction SilentlyContinue Remove-Variable -Name PathTest -Force -ErrorAction SilentlyContinue Remove-Variable -Name Result -Force -ErrorAction SilentlyContinue Remove-Variable -Name Counter1 -Force -ErrorAction SilentlyContinue Remove-Variable -Name LogFile -Force -ErrorAction SilentlyContinue Remove-Variable -Name LogFileKeepTime -Force -ErrorAction SilentlyContinue Remove-Variable -Name LogPath -Force -ErrorAction SilentlyContinue Remove-Variable -Name LogPaths -Force -ErrorAction SilentlyContinue Remove-Variable -Name Module -Force -ErrorAction SilentlyContinue Remove-Variable -Name Modules -Force -ErrorAction SilentlyContinue Remove-Variable -Name ModuleOverrideSourcePath -Force -ErrorAction SilentlyContinue Remove-Variable -Name ModuleDefaultSourcePath -Force -ErrorAction SilentlyContinue Remove-Variable -Name ModuleHKLMRegistryPath -Force -ErrorAction SilentlyContinue Remove-Variable -Name ModuleHKCURegistryPath -Force -ErrorAction SilentlyContinue Remove-Variable -Name serverName -Force -ErrorAction SilentlyContinue Remove-Variable -Name databaseName -Force -ErrorAction SilentlyContinue Remove-Variable -Name outputFolder -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportWithDBSchemaName -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportStoredProcedures -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportViews -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportUserDefinedFunctions -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportTables -Force -ErrorAction SilentlyContinue Remove-Variable -Name ExportLinkedServers -Force -ErrorAction SilentlyContinue $error.clear()