Stuck in Powershell sqlserver

From MS article SQL Server PowerShell

•A SQL Server provider, which enables a simple navigation mechanism similar to file system paths. You can build paths similar to file system paths, where the drive is associated with a SQL Server management object model, and the nodes are based on the object model classes. You can then use familiar commands such as cd and dir to navigate the paths similar to the way you navigate folders in a command prompt window. You can use other commands, such as ren or del, to perform actions on the nodes in the path.

Having a provider like this loaded allows you to treat the SQLServer the same way you would treat a drive (letter).

So most likely somewhere in your code you've used CD to move into the SQLServer provider (cd sqlserver:).

If you wanted to get back to d:\thefolder specifically you'd use cd d:\thefolder.

If you want to return to where you started when done, then you'll probably have to store the current directory location into a variable a the start of the script, and then use it with cd at the end of the script to change back to that original directory.

Or you could, as suggested by @alroc's in his comment, use the push-location and pop-location cmdlets to store and retrieve the current location.

this seems to works for me:

Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSqlCmd.sql" | out-null

Simply type C: on command prompt, to go back to the usual (e.g. C:\>) prompt.

Just for information, what may have triggered the SMO prompt (i.e. PS SQLSERVER:>) from normal C:\> prompt), is when you load the SMO assembly without the Out-Null (out-null suppresses the output returned by the cmdlet).

In other words, to avoid going inadvertently into the SMO prompt when loading the SMO assembly, simply pipe the Out-Null as below:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

As Microsoft has deprecated the LoadWithPartialName() method, use the Add-Type instead:

Add-Type -Path "your_path_to_assembly\Smo.dll"

Note: to get path to your assembly/dll - use the GetAssemblies() method as below:

[System.AppDomain]::CurrentDomain.GetAssemblies() | ? {$_.Location -match 'Smo.dll'}

HTH.