Image that you have a folder with some sql scripts and you need to run them. And the result to be saved , to a csv file.
and if a script is marked somehow, in this case with "_1" like xxxx_1, this script should be executed for each database in that server…
<#
run scripts
to a server
to a database
from a folder
To DO
pentru anumite scripturi, care au , de exemplu, in numele lor, la final ceva , de ex: _1 $string.SubString($string.Length-2)
pt ele ar trebuie sa trecem prin toate bazele de date existente pe server
$string.SubString($string.Length-2)
#>
Add-PSSnapin SqlServerCmdletSnapin100 # here live Invoke-SqlCmd
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
clear
$server = ‘SQLSERVERDEV2019′
$db =’master’
$filename = ”
#unde sunt scripturile care treb rulate
$SourceLocation = ‘E:\Documents\SQL Server Management Studio\Projects\Work\scripts_DBA\scripts2′
#unde exportam rezultatele
$FolderPathExport =’E:\Documents\SQL Server Management Studio\Projects\Work\scripts_DBA\output’
$NewOutputFolder = $FolderPathExport + "\" + $Server.replace(‘\’,’_’) + "_" + $(get-date -f yyyy_MM_dd)
#daca folderul exista sau nu, atunci il cream
#si daca exista ii punem si ora_minutele_sec
IF(!(Test-Path -path $NewOutputFolder))
{
New-Item -ItemType Directory -Force -Path $NewOutputFolder
}
ELSE
{
$NewOutputFolder =$NewOutputFolder +’__’ + $(get-date -f hh_mm_ss)
New-Item -ItemType Directory -Force -Path $NewOutputFolder
}
<# vedem ce baze de date sunt pe server #>
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server
$dbs=$s.Databases
#####
$listOfFiles = Get-ChildItem $SourceLocation
$listOfFiles | forEach-object {
#setam numele fisierul care reprezinta sursa pt rulare script
$fileNameInput = $_.FullName
#setam numele fisierului pt export
$fileName =”
$fileName = $NewOutputFolder+ "\" + $_.BaseName.replace(‘ ‘,’_’)
IF( $_.BaseName.SubString($_.BaseName.Length-2) -eq’_1′)
{
#daca avem -1 in coada, atunci trecem prin toate bazele de date
$dbs | ForEach-Object {
$fileNameInterior = $fileName + "_" + $_.Name + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
Invoke-sqlcmd -ServerInstance $server -Querytimeout 600 -Database $db -InputFile $fileNameInput| Export-Csv -Path $fileNameInterior -NoTypeInformation
}
}
ELSE
{
#buna , dar este inlocuita cu $fileName = $($FolderPathExport + "\" + $Server.replace(‘\’,’_’) ) + "_" + $_.BaseName.replace(‘ ‘,’_’) + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
$fileName = $fileName + "_" + $(get-date -f yyyy_MM_dd) + ".csv"
#$fileName
Invoke-sqlcmd -ServerInstance $server -Querytimeout 600 -Database $db -InputFile $_.FullName| Export-Csv -Path $fileName -NoTypeInformation
}
}
Sql Server 