Skip to content

Commit f5ae93b

Browse files
AlexTitenkoAlexTitenko
committed
Add 4 PowerShell scripts
1 Get-CmsHosts 2 Get-SQLUnattendedFile 3 Get-SystemInfo 4 Shred-XElogs
1 parent d19903d commit f5ae93b

4 files changed

Lines changed: 627 additions & 0 deletions

File tree

‎PowerShell/Get-CmsHosts.ps1‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
<#
2+
.SYNOPSIS This function queries a CMS instance and returns a list of instances Written by Mark Wilkinson @m82labs on Twitter, website m82labs.com
3+
4+
.PARAMETER cmdHost
5+
6+
.PARAMETER searchPattern !!NOT INJECTION SAFE!! this parameter simply gets inserted into a wildcard query on the list of available instances on the CMS. This parameter accepts a pipe delimeter list of patterns, allowing you to match instance names on multiple conditions.
7+
8+
.PARAMETER version The SQL Server version (build number) that should be running on the returned instances. !! This will query each instance to get the build version, use in conjuction with searchPattern !!
9+
10+
.EXAMPLE This will return all instances that start with 'Pattern' and are on SQL 2016 RTM
11+
Get-CMSHosts -searchPattern 'Pattern' -version '13.0.1605.1'
12+
13+
.EXAMPLE
14+
This can be used in a 'ForEach': ForEach ( $instance in Get-CMSHosts -searchPattern 'Pattern' -version '13.0.1605.1' ) { #Do some stuff }
15+
.NOTES
16+
Original link: http://tracyboggiano.com/archive/2017/04/query-multipleservers
17+
#>
18+
function Get-CmsHosts() {
19+
param(
20+
[CmdletBinding()]
21+
[string]$cmsHost = '',
22+
[string]$searchPattern = '',
23+
[string]$instanceList,
24+
[string]$version
25+
)
26+
27+
If ( $instanceList -and (Test-Path -Path $instanceList) ) {
28+
$results = Get-Content -Path $instanceList
29+
} Else {
30+
$pattern = ''
31+
32+
For ( $pat_i = 0; $pat_i -lt ($searchPattern.Split('|')).Count; $pat_i++ ) {
33+
If ( $pat_i -gt 0 ) { $pattern += " OR " }
34+
$pattern += "server_name LIKE '$($searchPattern.Split('|')[$pat_i])%'"
35+
}
36+
37+
[string]$query_get_servers = "SELECT DISTINCT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers WHERE {{searchPattern}}"
38+
$results = (Invoke-SqlCmd -query $query_get_servers.Replace('{{searchPattern}}',$pattern) -ServerInstance $cmsHost).server_name
39+
}
40+
41+
If ( $version ) {
42+
ForEach ( $instance In $results ) {
43+
Try {
44+
If ( (Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('productversion') AS v" -ServerInstance $instance -ConnectionTimeout 1 -QueryTimeout 1 -ErrorAction Stop).v -ne $version) {
45+
$results = $results | Where-Object { $_ -notmatch $instance }
46+
}
47+
} Catch {
48+
$connect_error += 1
49+
Write-Host "failed: $($_.Exception.Message)" -ForegroundColor White -BackgroundColor Red
50+
$results = $results | Where-Object { $_ -notmatch $instance }
51+
continue
52+
}
53+
}
54+
}
55+
56+
If ( $connect_error ) {
57+
Write-Host " -[$($connect_error) instance(s) skipped due to connection error]- " -ForegroundColor Red -NoNewline
58+
}
59+
60+
return $results
61+
}
62+
63+
64+
Get-CmsHosts -InstanceList 'c:\temp\servers.txt' | % { New-PSSession -ComputerName $_ | out-null}
65+
$sessions = Get-PSSession
66+
67+
$scriptblock = {
68+
$query = @"
69+
SELECT @@VERSION
70+
"@
71+
Invoke-Sqlcmd -Query $query
72+
}
73+
74+
Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView
75+
$sessions | Remove-PSSession
76+

‎PowerShell/Get-SQLUnattendedFile.ps1‎

Lines changed: 160 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,160 @@
1+
<#
2+
.Synopsis
3+
The objective of the script is to make use of Input file in which it consists of list of database server instances as a source for various parts of the script.
4+
5+
.Description
6+
This article is taking the sledge hammer approach and searching for mdf's and then comparing them against files which are available on the listed drive.
7+
Function to log Output and display the details on the console.
8+
9+
.Parameter InputFile
10+
Path to the file where the input details are saved.
11+
Example: c:\InputServer.txt
12+
13+
.Parameter LogFile
14+
The file logs all the information about the detached files or orphan file along with its size. This also contains the source of the server.
15+
16+
.Example
17+
Write-Log -Message "$($Server) is reachable and starting the process " -Logfile $Logfile
18+
19+
.Example
20+
Write-Log -Message "$(.Server) message " -Logfile $Logfile
21+
22+
.Example
23+
Get-SQLUnattendedFile -inputfile c:\server.txt -logfile c:\DetachedFileList.txt
24+
25+
.Link
26+
https://powershellsql.wordpress.com/ Jump
27+
https://www.sqlshack.com/multi-server-script-find-orphaned-data-files-using-powershell
28+
29+
#>
30+
31+
Function Get-SQLUnattendedFile
32+
{
33+
[CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='Low')]
34+
Param(
35+
[Parameter(Mandatory=$true,
36+
Position=0)]
37+
[String]$inputFile,
38+
[Parameter(Mandatory=$true,
39+
Position=1)]
40+
[String]$logfile,
41+
[Parameter(Mandatory=$true,
42+
Position=2)]
43+
[String]$extension='mdf'
44+
)
45+
46+
#Prepare Log file
47+
48+
if (Test-Path $logFile) {
49+
Remove-Item $logFile
50+
}
51+
52+
$ErrorActionPreference = 'Stop'
53+
54+
$sqlservers = Get-Content $inputFile
55+
56+
# Prepare headers for the log file for each execution of script
57+
58+
Add-Content $logFile "#################################################################"
59+
Add-Content $logFile "Unattended Database File Report"
60+
Add-Content $logFile "Generated $(get-date)"
61+
Add-Content $logFile "Generated from $(gc env:computername)"
62+
Add-Content $logFile "#################################################################"
63+
64+
Function Write-Log {
65+
[CmdletBinding()]
66+
Param(
67+
[Parameter(Mandatory=$False)]
68+
[ValidateSet("INFO","WARN","ERROR")]
69+
[String]
70+
$Level = "INFO",
71+
72+
[Parameter(Mandatory=$True)]
73+
[string]
74+
$Message,
75+
76+
[Parameter(Mandatory=$False)]
77+
[string]
78+
$logfile
79+
)
80+
81+
$Stamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
82+
$Line = "$Stamp $Level $Message"
83+
If($logfile) {
84+
Add-Content $logfile -Value $Line
85+
}
86+
Else {
87+
Write-Output $Line
88+
}
89+
}
90+
91+
92+
Try{
93+
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
94+
95+
#for printing found instances data uncomment next line
96+
#$Instances
97+
98+
foreach ($instance in $sqlservers)
99+
{
100+
If (!(Test-Connection $instance -count 1 -quiet)) {
101+
Write-host "$($instance) is not reachable"
102+
}
103+
else
104+
{
105+
#Write the Progress to console
106+
write-host "$($instance) is reachable and starting the process "
107+
108+
#Creating PowerShell custom objects
109+
$colAttachedMDFs = @()
110+
$files =@()
111+
112+
#Connect to the given instance. Piping to Out-null to avoid showing loading echo in output
113+
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($instance)
114+
115+
#get a list of all attached database file names
116+
foreach ($db in $srv.Databases )
117+
{
118+
foreach ($fg in $db.Filegroups)
119+
{
120+
foreach ($file in $fg.Files)
121+
{
122+
#Adding to list of attached DBs
123+
$colAttachedMDFs += $file.Filename
124+
}
125+
}
126+
}
127+
128+
#select local logical drives
129+
$drives=(get-wmiobject -class “Win32_LogicalDisk” -ComputerName $instance) | ?{$_.drivetype -eq 3 -and ($_.deviceID -eq "F:" -OR $_.deviceID -eq "G:" -OR $_.deviceID -eq "H:" -OR $_.deviceID -eq "I:" -OR $_.deviceID -eq "J:" )} | foreach-object {$_.name}
130+
#cycle over drives
131+
foreach ($drive in $drives)
132+
{
133+
$filter = "extension='$extension' AND Drive='$drive'"
134+
$files +=Get-WmiObject -Class CIM_Datafile -Filter $filter -ComputerName $instance |select name,FileName,@{Name="FileSizeMB";Expression={[math]::Round($_.FileSize/1MB,2)}}
135+
}
136+
137+
#$files
138+
foreach ($mdf in $files)
139+
{
140+
if (-not ($colAttachedMDFs -contains $mdf.name))
141+
{
142+
#Adding to list of unattached DBs
143+
$colMDFsToAttach += $mdf.name + [Environment]::NewLine
144+
Write-Log -Message "On $($instance) -> The filename $($mdf.FileName) in this path $($mdf.name) with a size of $($mdf.fileSizeMB) MB is left unattended " -Logfile $Logfile
145+
}
146+
}
147+
148+
}
149+
}
150+
}
151+
Catch{
152+
#Catch error, rethrow and raise exit code
153+
$_
154+
}
155+
$colMDFsToAttach
156+
}
157+
158+
Get-SQLUnattendedFile -inputfile c:\server.txt -logfile c:\DetachedFileList.txt
159+
160+
Invoke-item c:\DetachedFileList.txt

0 commit comments

Comments
 (0)