$colItems = Get-ChildItem $startFolder | Where-Object {$_.PSIsContainer -eq $true} | Sort-Object
foreach ($i in $colItems)
{
$subFolderItems = Get-ChildItem $i.FullName -recurse -force | Where-Object {$_.PSIsContainer -eq $false} | Measure-Object -property Length -sum | Select-Object Sum
$i.FullName + ” — ” + “{0:N2}” -f ($subFolderItems.sum / 1GB) + ” GB”
}
powershell
Getting the serial number for a PhysicalDrive
I don’t find a solution to get the required info in a single script, but this way will help to find the serial number for the physicaldrive.
- run below wmic command for command prompt to get the disk number and serial number
- WMIC disk drive get name, serial number
- run below PowerShell script to get disk number and size. by comparing the size of a disk in file explorer we can get the drive name, then get the serial number from the first step.
- Function Main {
- $diskdrives = get-wmiobject Win32_DiskDrive | sort Index
- $colSize = @{Name=’Size’;Expression={Get-HRSize $_.Size}}
- foreach ( $disk in $diskdrives ) {
- $scsi_details = ‘SCSI ‘ + $disk.SCSIBus + ‘:’ +
- $disk.SCSILogicalUnit + ‘:’ +
- $disk.SCSIPort + ‘:’ +
- $disk.SCSITargetID
- write $( ‘Disk ‘ + $disk.Index + ‘ – ‘ + $scsi_details +
- ‘ – ‘ + ( Get-HRSize $disk.size) )
- $part_query = ‘ASSOCIATORS OF {Win32_DiskDrive.DeviceID=”‘ +
- $disk.DeviceID.replace(‘\’,’\’) +
- ‘”} WHERE AssocClass=Win32_DiskDriveToDiskPartition’
- $partitions = @( get-wmiobject -query $part_query |
- sort StartingOffset )
- foreach ($partition in $partitions) {
- $vol_query = ‘ASSOCIATORS OF {Win32_DiskPartition.DeviceID=”‘ +
- $partition.DeviceID +
- ‘”} WHERE AssocClass=Win32_LogicalDiskToPartition’
- $volumes = @(get-wmiobject -query $vol_query)
- write $( ‘ Partition ‘ + $partition.Index + ‘ ‘ +
- ( Get-HRSize $partition.Size) + ‘ ‘ +
- $partition.Type
- )
- }
- function Get-HRSize {
- [CmdletBinding()]
- param(
- [Parameter(Mandatory=$True, ValueFromPipeline=$True)]
- [INT64] $bytes
- )
- process {
- if ( $bytes -gt 1pb ) { “{0:N2} PB” -f ($bytes / 1pb) }
- elseif ( $bytes -gt 1tb ) { “{0:N2} TB” -f ($bytes / 1tb) }
- elseif ( $bytes -gt 1gb ) { “{0:N2} GB” -f ($bytes / 1gb) }
- elseif ( $bytes -gt 1mb ) { “{0:N2} MB” -f ($bytes / 1mb) }
- elseif ( $bytes -gt 1kb ) { “{0:N2} KB” -f ($bytes / 1kb) }
- else { “{0:N} Bytes” -f $bytes }
- }
- }
- Main
List all the folders that contain only one file.
Get-ChildItem -Path “C:\Users\gollapudiy\Downloads” -recurse | Where {$_.PSIsContainer -and @(Get-ChildItem $_.Fullname | Where {!$_.PSIsContainer}).Length -eq 1} | Select-Object name |out-file “C:\Users\gollapudiy\Downloads\singlefiles.csv”
with the predicate $_.psiscontainer we can filter the directories.
List all subfolders under a target path/folder with PowerShell
#This script will list out all folders under a target path/folder and save to a CSV file.
Get-ChildItem -Path “C:\Users\gollapudiy\Downloads” -Directory -Force -ErrorAction SilentlyContinue | Select-Object name | export-csv C:\Users\gollapudiy\Downloads\files.csv
#-Recurse — to get all subfolders inside a subfolders
Search for a file on all drives using Powershell
Get-ChildItem -Path c:\,E:\,L:\,M:\,S:\,T:\,U:\ database1.ldf -Recurse | select name |Format-Table -AutoSize| Out-File U:\ldf.txt
Get server boot time using DBAtools
Get-DbaUptime -sqlinstance server1,server2,server3| select sqlserver, windowsboottime
Get the files older than 30 days in a folder using Powershell
$Path = “U:*.BAK”;
$DaysToKeep = -30;
$CurrentDate = Get-Date;
$DatetoDelete = $CurrentDate.AddDays($DaysToKeep)
$DatetoDelete
Get-ChildItem $Path -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | out-file “C:\Users\yogi\Desktop\oldbackup.txt” -Append
get the backup drive info for remote servers using powershell
step 1: save this below tsql as backup_drive_details.sql file
SELECT
distinct left(physical_device_name,1) AS Backup_Drive,@@SERVERNAME
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
group by physical_device_name
step 2: replace the file paths with yours in below powershell block
$servers = Get-Content “C:\Users\yogi\Desktop\serverlist.txt”
foreach ($ser in $servers)
{
$a =Invoke-Sqlcmd -inputfile “C:\Users\yogi\Desktop\backup_drive_details.sql” -serverinstance $ser | out-file -FilePath “C:\Users\S74697580WUS\Desktop\backup_drive_details.txt” -Append
}
get the list of older backup files on a drive – powershell
— replace trn with bak to get the full backup file info
$Path = “U:*.trn”;
$DaysToSkip = -30;
$CurrentDate = Get-Date;
$Datetarget = $CurrentDate.AddDays($DaysToSkip)
Get-ChildItem $Path -Recurse | Where-Object { $_.LastWriteTime -lt $Datetarget } | out-file “C:\Users\yogi\Desktop\oldbackup.txt” -Append
Get the instant file initialization status using Powershell
TSQL:
SELECT @@SERVERNAME AS [Server Name] ,
— RIGHT(@@version, LEN(@@version) – 3 – CHARINDEX(‘ ON ‘, @@VERSION)) AS [OS Info] ,
— LEFT(@@VERSION, CHARINDEX(‘-‘, @@VERSION) – 2) + ‘ ‘
— + CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR(300)) AS [SQL Server Version] ,
service_account ,
instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE ‘SQL Server (%’
- save above Tsql as ifiq.sql
- save the server names in a text file as serverlist.txt
- make sure all paths are updated and run below script in powershell
$servers = Get-Content “C:\Users\Desktop\serverlist.txt”
$output =foreach ($ser in $servers)
{
Invoke-Sqlcmd -inputfile “C:\Users\Desktop\ifiq.sql” -serverinstance $ser
}
$output | format-table |out-file -FilePath “C:\Users\Desktop\ifiq.txt” -Append
selectstarfromyogi 