top of page

PowerShell Server Baseline | SQL Insert | Input Script

Online Installation

Install-Module -Name SqlServer

Common CMDlets

Instance Management: Get-SqlInstance Connect-SqlInstance Database Management: Get-SqlDatabase New-SqlDatabase Remove-SqlDatabase Backup-SqlDatabase Restore-SqlDatabase SQL Agent Management: Get-SqlAgentJob New-SqlAgentJob Remove-SqlAgentJob

Steps to Load SqlServer Module from File

  1. Ensure you have the module files: Verify that you have the SqlServer module files on your local machine. The module folder should contain a .psd1 (module manifest) file and possibly other related files.

  2. Determine the path to the module: Identify the directory where the module files are stored. For example, if the SqlServer module files are located in C:\Modules\SqlServer.

  3. Import the module: Use the Import-Module cmdlet to load the module by specifying the path to the module manifest file.

Example Command

Assuming the SqlServer module is located at C:\Modules\SqlServer, you can import it using the following command:

Import-Module -Name "C:\Modules\SqlServer\SqlServer.psd1"

Use of SQL and PowerShell

PowerShell SQL Module Insert

1) The initial run will execute the Input Script adding the server names

as well as all running services found on each system into a SQL database table.

 

#=====================================================

# Get Service Information and Export to CSV 

#=====================================================

#Get List of Online Accessible Servers
$GetServers = Get-Content C:\temp\ServerList.txt 

#Query Each Server for a List of Running Services
$Results=Invoke-Command -ComputerName $GetServers -ScriptBlock {Get-Service | Where{$_.Status -eq "Running"}}

#Export Select Results to File and for Reference File
$Results | Select PSComputerName,Name, DisplayName | Export-csv C:\temp\MyServices.csv -NoTypeInformation 

#=====================================

# Inset Data Into SQL DB Table 

#=====================================

#Import Data For Insert to SQL 
$ImportData=Import-CSV C:\temp\MyServices.csv

#Downloads and Installs SQL Module and Will Overwrite and Older Version Module Installed
Install-Module -Name SqlServer -AllowClobber

#Loop Through Each Record and Add to SQL Table
Foreach($Comp in $ImportData){

    $insertquery="
                INSERT INTO [dbo].[ComputerServices]
                ([PSComputerName]
                ,[Name]
                ,[DisplayName])
         VALUES
               ('$($Comp.PSComputerName)'
               ,'$($Comp.Name)'
               ,'$($Comp.Displayname)')"

    #Command to Insert to SQL     
    Invoke-Sqlcmd -ServerInstance Desktop-Main -database ServerManager -Query $insertquery
}

bottom of page