Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory
I just ran into this on SQL Server 2017 (on Windows Server 2016) and found your question in the process. I then went and fired up our older SQL Server 2014 (Windows Server 2012) machine and found the same issue. Here's a couple options everyone can try to save some time (as I realize the question is kind of old and I'm assuming the OP found a solution already):
1) At a powershell command run: Install-Module -Name SqlServer
(you might need -AllowClobber parameter)
The thing here is that module is installed to the following path on both 2012 and 2016 servers:
C:\Program Files\WindowsPowerShell\Modules\SqlServer (rather than Microsoft SQL Server\120\Tools\PowerShell\Modules)
2) Another option is to try the SQLPS
module by changing in your script:
Import-Module -Name SqlServer
To:
Import-Module -Name SQLPS
You will find SQLPS
in the Microsoft SQL Server directory structure here:
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules
My script works with both SQL modules now, but the output actually looks better with SQLPS at this point...