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

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:

Accessing regular file locations is no problem and just works.

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

asked 2016-08-15 by vstrien

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.

answered 2016-08-15 by Andy Mallon