PowerShell can't see network locations after SQLPS module is loaded

Question

I’m trying to automate a script that backups a database in one environment, copies it to the other, then restores it there. But there’s some strange behavior going on.

First, I try to access a network location. Works like a bliss:

C:\> Get-ChildItem \\remote-server\e$

Returns

Directory: \\remote-server\e$
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----         19-3-2015     11:49            Program Files

However, as soon as I import the Sqlps module (which is the collection of SQL Server Management Objects - SMO) for interacting with the database, the networked locations can’t be found anymore:

PS C:\> Import-Module Sqlps -DisableNameChecking;
PS SQLSERVER:\> Get-ChildItem \\remote-server\e$

Returns:

Get-ChildItem : Cannot find path '\\remote-server\e$' because it does not exist.
At line:1 char:1
+ Get-ChildItem \\remote-server\e$
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (\\remote-server\e$:String)[Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Accessing regular file locations is no problem and just works.

How can I access network locations after having loaded the SQLPS module / SMO?

Asked2016-08-15byvstrien

Answer

When you run the Import-Module Sqlps -DisableNameChecking; command, your current location is immediately changed to PS SQLSERVER:\$gt;. Being “in” SQLSERVER prevents you from using Get-ChildItem related to a drive location.

First, you will need to change your current location back to a drive location with Set-Location or cd. Then, you will be able to use Get-ChildItem for the folder, then navigate back to SQLSERVER:\ (if necessary):

Example:

    PS C:\ > Import-Module Sqlps -DisableNameChecking;
    PS SQLSERVER:\> Set-Location C:\
    PS C:\> Get-ChildItem \\remote-server\e$
    PS C:\> Set-Location SQLSERVER:\

I should also note that the behavior of changing your location on importing the module is a bug that was fixed in the SQLSERVER module during SQL Server 2016 development. In the future, your current location would not be switched to SQLSERVER:\, so you would not need to Set-Location back to a drive location.

Answered2016-08-15byAndy Mallon