# VERSION: 1.4.0
# AUTHOR: F. Bischof (info@meer-web.nl)

#Variable to hold variable  
$SQLServer = "dbserver1"  
$SQLDBName = "database"  
$delimiter = ";"

#### Start script ####
# Function to log to file
Function SetLog {
  $DATUM = Get-Date -UFormat "%d-%m-%Y %H:%M:%S"
  "${DATUM}: ${LogMsg}" | Out-File -Append -FilePath ${LogFile}
}


# Check Powershell version 7
if (($PSVersionTable.PSVersion.Major) -ne 7) { Write-Host "Please use Powershell 7"; exit }
# Check if SqlServer module is installed
if ($null -eq ((Get-InstalledModule).Name | select-string -Pattern "SqlServer")) { Install-Module -Name SqlServer }
$Location = (Get-Location).path
$TimeStamp = (get-date).ToString("yyyyMMdd-HHmm")
$LogFile = "$Location\$TimeStamp\SqlDump.log"
mkdir ${TimeStamp}
Write-Host "=== STARTING QUERIES ON $SQLDBName ==="
$LogMsg = "Starting queries on $SQLDBName"; SetLog

function SqlQueryExec {
	#SQL Query  
    Write-Host "Starting - ${SqlFile}"
    $LogMsg = "- $SqlFile"; SetLog
    if ($true -eq (test-path -path "${SqlFile}.sql")) {
		$SqlResult = Invoke-Sqlcmd -InputFile "${SqlFile}.sql" -ServerInstance $SQLServer -Database $SQLDBName -QueryTimeout 0 -TrustServerCertificate
		$SqlResult | Export-CSV -Path "$Location\$TimeStamp\${SqlFile}.csv" -Delimiter $delimiter -NoTypeInformation
		Write-Host "Done - ${SqlFile}"
    } else {
        Write-Host "ERROR - ${SqlFile}.sql does not exist!"
        exit
    }
}

# Sql File without extension
$SqlFile = 'Sql_query_file_1'; SqlQueryExec
$SqlFile = 'Sql_query_file_2'; SqlQueryExec
$SqlFile = 'Sql_query_file_3'; SqlQueryExec
$SqlFile = 'Sql_query_file_4'; SqlQueryExec

$LogMsg = "Done!"; SetLog