49 lines
1.6 KiB
PowerShell
49 lines
1.6 KiB
PowerShell
# 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
|