When I was thinking to access/connect SQL Server using PowerShell in one of the following ways
1. .NET (System.Data.SqlClient)
2. SSMO (SQL Server Management Objects)
3. SQL Server PowerShell
I was actually trying with third method. Yes! unfortunately, It failed. Let me tell you, what I tried and how I resolved.
Open PowerShell ISE and try the below line of command
Connecting SQL from local machine
Import-Module SqlServer
I got the Err...
Import-Module : The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.
I thought, we don't have the Module in my environment and check the available Modules named "SQLServer" finally
Get-Module -ListAvailable -Name *SQLServer*
I could not find any such a Modules
I tried to Install the Module in my environment,
The PowerShell Gallery is the repository from Microsoft
Click and open the specific Module "SQLServer"
I Installed the Module
Install-Module -Name SqlServer -RequiredVersion 21.1.18245 -AllowClobber
Let the process download the required Package and Install
Once the Installation complete
Let me try to check the Module installed
Get-Module -ListAvailable -Name *SQLServer*
Yes! I can see the Module Installed
Let me try to Import the Module
Import-Module SqlServer
Let us get the List of Databases from my local SQL Instance
Get-SqlDatabase -ServerInstance SQLBUDDY\SQL2019
See, I got the all available Databases in my SQL Instance "SQLBUDDY\SQL2019"
Note: I am using Logged-In same credential to connect with my local SQL Server Instance
