top of page

PowerShell SQL Update Computer

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"

SQL Delete & Update

5) Finally we will have a Update Computer Script to allow a user to  update the services for a server already listed in the system. This  allows updates to the system, for example if IIS was installed;  these running services will need added to the SQL Table for that server.

The execution of the Update Computer Script will simply delete  all the service entries for the server and then add the current  running services to the SQL Database table. 

#=====================================================
# 1) Import Server List from TxT & Get Current Running Service List

#     1.A) Exports Data to CSV FIle for later use in this script.

# 2) Deletes Corresponding Server Information in Databsae Table (Stale)

# 3) Import CSV From #1 Output and Insert to SQL
#=====================================================

# 1) Import Server List from TxT & Get Current Running Service List
$UpdateServers = Get-Content C:\temp\Computers.txt

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

 #1.A) Exports Data to CSV FIle for later use in this script.
$UpdateResults | Select PSComputerName,Name, DisplayName | Export-csv C:\temp\UpdateServices.csv -NoTypeInformation 
$UpdateImportData=Import-CSV C:\temp\UpdateServices.csv

# 2) Deletes Corresponding Server Information in Databsae Table (Stale Data)
$ReturnValue2=[System.Windows.MessageBox]::Show('Would you like to update SQL Table?','Game input','YesNo','Error')

If($ReturnValue2 -eq "Yes"){

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

    #List of Unique Servers  
    $UpdateServerList=$UpdateImportData.PSComputerName | Select-Object -Unique 

    #Da Loop
    Foreach($UpdateComputer in $UpdateServerList){

        # Delete Each Computer in List by Server Name
        Invoke-Sqlcmd -ServerInstance Desktop-Main -ErrorAction SilentlyContinue -database ServerManager -Query "Delete From ComputerServices Where PSComputername = '$UpdateComputer'"    

    }
}

# 3) Import CSV From #1 CSV Export and Insert to SQL
Foreach($UpdateComp in $UpdateImportData){

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

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

}

bottom of page