PowerShell SQL Refresh 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
-
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.
-
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.
-
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"
PowerShell SQL Module Insert
4) The Refresh Script will take the report.txt file as input to
add the missing servers to the SQL database table.
​
#=====================================================
# Get Service Information and Export \ Import CSV
#=====================================================
#Get List of Servers from Report.txt
$GetNewServers = Get-Content C:\temp\Report.txt
#Query Each Server for a List of Running Services
$NewResults=Invoke-Command -ComputerName $GetNewServers -ScriptBlock {Get-Service | Where{$_.Status -eq "Running"}}
#Export & Import Select Results
$NewResults | Select PSComputerName,Name, DisplayName | Export-csv C:\temp\MyNewServices.csv -NoTypeInformation
$NewImportData=Import-CSV C:\temp\MyNewServices.csv
#Popup to Assure you Want to Add to SQL
$ReturnValue=[System.Windows.MessageBox]::Show('Would you like to update SQL Table?','Game input','YesNo','Error')
If($ReturnValue -eq "Yes"){
#=====================================
# Insert New Data Into SQL DB Table
#=====================================
#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 $NewImportData){
$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
}
}