Run SQL Scripts using PowerShell

Run SQL Scripts using PowerShell

We all have the need to run SQL Scripts against Multiple Servers on Daily Basis. Sometimes, Its cumbersome to manually run the scripts against the server using Management Studio.
This powershell Script comes handy to run multiple scripts against specified SQL Server.

 $inputfilefolder = "C:\Dev\svn\database\Projects\NewMexico\DBSetup\"

 $outputfilefolder = "C:\Dev\svn\database\Projects\NewMexico\DBSetup\Output\"

 $dbname = "AdventureWorks"

 $Servername = "DEVSQL\DEV2008R2"

 $filearry = @("01.01_ObjectsSetup.sql","02_DomainDataLoad.sql" ,"03_TenantMandatoryData.sql" ,"04_NMScienceStandards.sql" ,"07_DemoOrganizationLoad.sql"
 ,"05_DistrictOrganizationLoad.sql","06_SchoolOrganizationLoad.sql", "08_DemoStudentsLoad.sql" ,"10_DemoTeacher_ProgramProperties_Setup.sql" , "03_TenantMandatoryData.sql")

foreach ($file in $filearry)
 {
 Write-Host $file

 $inputfile = $inputfilefolder + $file

 $outputfile = $outputfilefolder +$file+".rpt"

 Invoke-Sqlcmd -ServerInstance $Servername -Database $dbname -InputFile $inputfile | Out-File -filePath $outputfile
}