SQLASCmdlets was not loaded because no valid module file was found in any module directory

Question

I have a script that fails due to missing SQLASCmdlets module.

The specified module 'SQLASCmdlets' was not loaded because no valid module file was found in any module directory.

In order to run this I know I needed SQLPS which I can found using

Get-Module -ListAvaiable

ModuleType Version    Name
---------- -------    ---- 
Manifest   1.0        SQLPS

I have installed - SQL Sever 2016 - SQL Management Studio 2017

Error:

VERBOSE: ******************************
VERBOSE: The specified module 'SQLASCmdlets' was not loaded because no valid module file was found in any module directory.
VERBOSE: Errors: 1
VERBOSE: ******************************
VERBOSE: System.IO.FileNotFoundException: The specified module 'SQLASCmdlets' was not loaded because no valid module file was found in any module directory.
VERBOSE: ScriptHalted The specified module 'SQLASCmdlets' was not loaded because no valid module file was found in any module directory.

When I rerun my script again it works. How do I make it work on first time?

The code that I used is:

Import-Module "SQLPS" -DisableNameChecking


$sqlsvr = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server("WIN-C0BP65U3D4G")
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$devicetype = [Microsoft.SqlServer.Management.Smo.DeviceType]::File

$files = gci C:\psbackups

$BackupFiles = @()

foreach ($file in $files){
    $restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($file.FullName,$devicetype)

    $restore.Devices.add($restoredevice)
    $errcnt=0
    try{
        $restore.ReadMediaHeader($sqlsvr)
    }
    catch [System.Exception]{
        Write-Output "$file is not a sql backup file `n"
        $errcnt =1
    }
    finally {
        if ($errcnt -ne 1){
            Write-Output "$file is a sql backup file `n"
            $BackupFiles += $file
        }
        $errcnt = 0
    }
    $restore.Devices.remove($restoredevice)
    Remove-Variable restoredevice
}

Based on this article PowerShell to restore database

cpoDesign cpoDesign · Accepted Answer · 2017-12-19T15:37:01

After 2 hours of research I have found the solution.

Install SqlServer package

https://www.powershellgallery.com/packages/SqlServer/21.0.17199

2.I have updated my script to choose specific version in case it is installed

# import modules 
if ((Get-Module -ListAvailable | where-object {($_.Name -eq 'SqlServer') -and ($_.Version.Major -gt 20) } |Measure).Count -eq 1){
    # implementation of new sql modules migated into new location
    Import-Module SqlServer -DisableNameChecking -Verbose
}
else{
    # fallback for SQLPS 
    Import-Module SQLPS -DisableNameChecking -Verbose
}

Ensure I have new session for your PowerShell.

SQLASCmdlets was not loaded because no valid module file was found in any module directory

3 Answers