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