PowerShell based SQL script execution I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server.

1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

The 3 steps described above is my requirement. Someone please help on this..Any help would be really appreciated..

Thanks in advance..
Note: Before I get into sample code on one way to go about this, I recommend you make every attempt at grasping the programmatic interaction between SQL Server and PowerShell.  It is not hard, and by muscling through a few scripts from given requirements like the ones above your proficiency will exponentially grow.


  1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)


Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServerName = Read-Host "Server name"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

$SqlServer.ConnectionContext.LoginSecure = $false

$SqlServer.ConnectionContext.Login = Read-Host "Login"
$SqlServer.ConnectionContext.SecurePassword = Read-Host "Password" -AsSecureString



  2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)


$SqlScripts = @()

while ($true) {
    $ScriptLocation = Read-Host "Enter location (""x"" if no more needed)"
    if ($ScriptLocation.ToLower() -eq "x") {
        break
    }
    $SqlScripts += $ScriptLocation
}



  3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.


foreach ($SqlScript in $SqlScripts) {
    try {
        $SqlServer.Databases["master"].ExecuteNonQuery((Get-Content -Path $SqlScript))
    }
    catch {
        Write-Error $_.Exception
    }
}


As for your last statement "After the execution, then it should again ask user to continue or exit", I'm not quite sure what you're looking to do there.  Ask the user to continue doing what?  Regardless, the above sample code should give you quite a head start on your requirement.  Test this logic out in a non-production environment to ensure it does what you want and rework accordingly.  There are a couple of assumptions here (such as you're not looking for a return from the script, which is why I used ExecuteNonQuery(), I don't know your databases layout so it's going to execute in the context of master, so on and so forth).