Unblock Downloaded Applications Using PowerShell

    Anyone who has downloaded applications from the Internet has seen this warning:

    And many probably know you can go into properties and unblock that program:

    However, it’s also possible to find and unblock those files in PowerShell. The “Unblock-File” command will unblock the application even if the [Unblock] button has been removed by Group Policy.

    To find all the files in a folder that are blocked, you can use the

    "Zone.Identifier" stream:
Get-Item * -Stream "Zone.Identifier" -ErrorAction SilentlyContinue

    And, you can pipe that into select to see only the filenames of the blocked executable files:

    Get-Item * -Stream "Zone.Identifier" -ErrorAction SilentlyContinue | select FileName

    Source: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/unblock-file?view=powershell-5.1

    Categories: PowerShell, Scripts

    List All Files to CSV and Copy to Your Local Machine

    I recently realized that the backup job I’ve been creating hasn’t been properly deleting the files. Using this script I’m able to find all files in a folder (and it’s subfolders) so I can determine which files should be deleted. Since this is quicker to do on the remote server than my local machine, the last part is to copy the CSV to C:\Temp on my laptop. The date calls are just there as indicators of how long each step takes.

    $today=$(Get-Date -Format u).Substring(0,10).replace("-","") date Get-ChildItem -Path "\\UNCServer\backup\directory\" -Recurse | Select-Object DirectoryName,BaseName, Extension, Length, CreationTime | Export-Csv -Path D:\Install\FileListing_$($today).csv date copy "D:\Install\FileListing_$($today).csv" "\\tsclient\C\Temp\" date
    Categories: PowerShell, Scripts

    Delete Old SQL Server Backups

    Part of any good backup plan is deleting  the old backups you don’t need anymore. This PowerShell script will find all the full and tLog backups older than 31 days and delete them. Because this is meant to be run as a step in a SQL Agent job, PowerShell will default to a SQL Server context. Starting the script with CD C: will switch that to the file system. Without this line you may get an error that the path cannot be found.

    CD C:
$backupPath = "C:\Backup\full\User\"
$tLogPath = "C:\Backup\tLog\"

$limit = (Get-Date).Date.AddDays(-31)
Get-ChildItem -Path $backupPath | Where-Object { $_.LastWriteTime -lt $limit } | Remove-Item -Force
Get-ChildItem -Path $tLogPath | Where-Object { $_.LastWriteTime -lt $limit } | Remove-Item -Force
    Categories: PowerShell, Scripts, SQL Server