Category: PowerShell

PowerShell & Windows Admin Blog Archives, by Peter Whyte (SQL Database Administrator). Includes a lot of WSL posts & more…

  • Introducing DBASco: SQL Server Database Admin Blog

    Introducing DBASco: SQL Server Database Admin Blog

    Greetings Tech People!

    If you’ve been following my technical journey on peter-whyte.com, I’m excited to share a new platform that dives into the world of Database Reliability Engineering and beyond – welcome to DBASco.com!

    At DBASco.com, I share my latest technical writings, focusing on a spectrum of topics catering to IT enthusiasts, database/system administrators, and those passionate about the software behind reliability engineering. The primary categories align with the ones you’re familiar with at peter-whyte.com, ensuring a seamless transition for any dedicated readers.

    This site here is now my secondary tech blog site. If you’ve been on here before you will have seen a lot of display ads, which are disabled for the time being. I was using Ezioc, and I was earning around $50-60 per month, until I neglected things for too long during 2023 and there was a drop in earnings. The site certificate had issues during 2023 Q4, and we are back fully operational as of this week.

    My enjoyment is the technical writing, refining, and seeing where tests lead me, exploring my passion for the things I do as a career and on the side. I hope you enjoy what I write, and it serves a purpose if visiting via Online Search to understand a concept or resolve an issue.

    SSMS Logo

    DBASco Blog Categories

    DBASco: SQL Server: Explore Microsoft SQL Server with in-depth insights into queries, optimization techniques, and best practices. Uncover the holistic experience, including the realm of Windows Administration that intertwines seamlessly with the life of a SQL Server DBA.

    DBASco: PowerShell: Dive into the enchanting world of PowerShell scripting with hands-on guides, expert tips, and powerful tricks. Unearth the magic of automation as you streamline your workflow with carefully crafted scripts and comprehensive guides.

    DBASco: WSL and Linux: Journey into the world of Windows Subsystem for Linux (WSL) and Linux, discovering the synergy between these platforms and unraveling the potential for seamless integration. Benefit from real-world experiences and extensive testing, providing you with valuable insights into cross-OS platform development.

    DBASco: Other: From RDMS’s like MySQL, Postgres, Redshift to semi-related technologies like AWS, and even technical guides on WordPress and website management – expect a wealth of knowledge that keeps you at the forefront of the ever-evolving tech landscape.

    SSMS Logo

    DBASco Latest Articles

    RedShift: Script to Show Running Queries
    Published on January 24, 2024
    Uncover the power of Amazon Redshift, a cloud-based data warehousing giant. Learn how to efficiently manage and analyze extensive datasets with a detailed script to reveal running queries.

    RedShift: Creating a User and Promoting to Superuser
    Published on January 24, 2024
    Unlock the secrets of Amazon RedShift as we guide you through creating a user and elevating their privileges to superuser status. Essential insights for effective cluster management.

    PowerShell Script: List Files with Sizes and Dates
    Published on January 23, 2024
    Master a practical PowerShell script that simplifies listing files in a directory. Dive into efficient file management with sizes and dates at your fingertips.

    Backing Up a Database in SQL Server Using Command
    Published on January 22, 2024
    Ensure the safety and integrity of your data with a comprehensive guide on backing up a database in SQL Server using command-line tools. A crucial aspect of database management unveiled.

    SQL Server Script: Get Last Backup Dates
    Published on January 22, 2024
    Stay informed about the last backup times, a critical element in maintaining data integrity. Enhance your SQL Server skills with a script that simplifies tracking backup dates.

    PowerShell: Counting Files, Words and Rows
    Published on January 22, 2024
    Embark on a PowerShell journey with a script that counts files, words, and rows. Discover the power of PowerShell for efficient data processing and analysis.

    Adding a Filegroup in SQL Server
    Published on January 21, 2024
    Learn the process of adding a new filegroup to a database in SQL Server. Elevate your database architecture skills with a step-by-step guide.

  • How to Set Environment Variables in PowerShell

    How to Set Environment Variables in PowerShell

    To get environment variables in PowerShell, we can use Get-ChildItem env: to list all the environment variables and $env: to return the value of a specific environment variable. This blog post should help explain this as well as how to set new values for Environment Variables.

    An environment variable is a value that is stored on a computer and can be accessed and used by programs to alter their behaviour. For example, in the AWS CLI you can set an environment variable called “AWS_REGION” and assign it a value such as “eu-west-1”. Then, you could have a program that reads this environment variable and uses it to specify the AWS region when making API requests.

    In PowerShell, you can access environment variables using a number of different cmdlets. This post covers cover two of the most common methods, Get-ChildItem and Get-Item.

    This post will cover the following:
    List All Environment Variables
    Get Value of a Specific Environment Variable
    Set New Environment Variable

    List All Environment Variables

    To list all of the environment variables on your system, use the Get-ChildItem cmdlet with the Env: drive. This displays a list of all the environment variables and their current values.

    You can also run gci which is an alias for Get-ChildItem as displayed in my screenshots below.

    # List all environment variables
    Get-ChildItem Env:
    
    PowerShell gci env:

    Get Value of a Specific Environment Variable

    To return the value of an environment variable in PowerShell, run the following:

    # Get the value of the aws_default_region environment variable
    gci Env:\AWS_DEFAULT_REGION
    
    # Do the same but alt (easier) syntax
    $env:AWS_DEFAULT_REGION
    
    PowerShell Show Environment Variable Value

    Set New Environment Variable

    To set a new value for the AWS_DEFAULT_REGION environment variable with PowerShell, use the following command:

    # Set the AWS Default Region Environment Variable
    $env:AWS_DEFAULT_REGION="eu-west-2"
    
    PowerShell Set Environment Variable

  • SSMS Silent Install

    SSMS Silent Install

    This is a note on how to silently install SQL Server Management Studio (SSMS) via command (PowerShell).

    SSMS is a tool most people use to administer SQL Server and to run SQL queries. If you need to install SSMS many times or on multiple computers, you may want to use the silent installation feature when automating the process.

    This ‘silent‘ installation allows you to install SSMS without any user interaction, making it quick and easy to deploy on multiple computers. In this demo, we are using the Standard Microsoft Installer Command-Line Quiet Option (/q).

    How to Silently Install SSMS

    To perform a silent installation of SSMS, you will need a copy of the SSMS installation files.
    You can download SSMS from this link here: SSMS Download (Microsoft Docs)

    Open Windows Terminal or any command prompt for PowerShell as Administrator and navigate to the directory where you have the SSMS installation files downloaded. Then run the msi exec command as shown:

    # Navigate to directory with ssms download
    cd ssms
    gci
    
    # Install SSMS (silently)
    .\SSMS-Setup-ENU.exe /q
    SSMS Silent Install

    The only parameter being used here is /q which specifies that the installation should be performed silently. So SSMS will install in the background and your terminal prompt will return to a command-ready state when the installation has been completed.

    SSMS will be installed silently on the host and be ready for use. That should be all there is to this one. Feel free to check out the SSMS Tips Tag for more useful information about this application.

  • How to Delete Files in PowerShell

    How to Delete Files in PowerShell

    This is a post on how to delete files in PowerShell, which will be useful when you need to delete files as part of a script in Windows.

    This post covers the following:
    # PowerShell: Delete a File
    # PowerShell: Delete a Folder
    # PowerShell: Delete Files in Subfolders Recursively

    I have a similar post on How to Create Files & Folders in PowerShell if of interest, and more random tips can be found in the PowerShell Tips Tag.

    PowerShell: Delete a File

    Remove-Item is the cmdlet to remove a directory or file in PowerShell. We can delete files within the current directory, or add the path parameter.

    I’m showing some syntax variations below. If you are passing a directory with spaces in the name then you will have to add quotes to the path.

    # Delete file in current directory
    Remove-Item -Name testFile.txt
    
    # Delete file in specified directory
    rm -Path C:\temp\demoFolder\testFile.txt
    
    # Delete all files in specified directory with .tmp file extension
    rm "C:\temp\*.tmp"
    Remove-Item PowerShell

    PowerShell: Delete a Folder

    We do not need to change any parameters to delete a file, just run the same command as above. The consideration when deleting folders is the files within them.

    The following demo script first deletes a folder within the current directory, and the second part checks if folders exist before deleting them.

    # Delete a folder in current directory
    rm .\demoFolder\
    
    # Delete folder if exists, force delete all files in folder
    # Does not delete files in sub-directories
    $path = "c:\temp\demoFolder"
    If((Test-Path $path) ){
        rm $path -Force -Recurse
    }
    PowerShell Delete Folder If Exists

    PowerShell: Delete Files in Subfolders Recursively

    For when you need to delete files in a folder and subfolders, the script below should help. Amend path and file type wildcard as needed.

    # Delete all .txt files in folder & subfolders
    $path = "c:\temp\demoFolder"
    If((Test-Path $path) ){
        Get-ChildItem $path -Include *.txt -Recurse | rm
    }
    PowerShell Delete Files in Folder and Subfolders

    Last additional note on this, I haven’t used gci within any of the PowerShell scripts above in this post. It’s an Alias of Get-ChildItem.

  • Create a New Firewall Rule with PowerShell

    Create a New Firewall Rule with PowerShell

    Create a New Firewall Rule with PowerShell

    This is a short post to share a PowerShell script that will create a new Windows Firewall Rule on a local computer.

    PowerShell Create New Firewall Rule

    We have to ensure that we run PowerShell as Administrator for this to work.

    # New firewall rule (run powershell as administrator)
    if (-not( Get-NetFirewallRule -DisplayName “Allow Inbound SQL (1433)” -ErrorAction SilentlyContinue)) { 
    New-NetFirewallRule `
        -DisplayName “Allow Inbound SQL - 1433” `
        -Direction Inbound `
        -Protocol TCP `
        -LocalPort 1433 `
        -Action Allow }
    PowerShell New Firewall Rule

    The above is creating a new firewall rule so incoming SQL Server (1433) traffic is allowed to happen on this computer.

    Other similar/useful posts I have on this include the following:
    # How to Check Windows Firewall Status with PowerShell
    # How to Enable Windows Firewall with PowerShell
    # How to Disable Windows Firewall with PowerShell

  • How to Check PowerShell Version

    How to Check PowerShell Version

    This post is a guide on how to check your PowerShell version on a Windows computer. I’m using $PSVersionTable to get this info.

    It’s important to keep all software as up-to-date as possible, we all know it. Being on the latest PowerShell version is especially required if you are utilising the latest features/cmdlets. For example, you might one day attempt to copy and run a script, and realise that it contains a command that is not recognised on a lower version of PowerShell.

    To check your PowerShell version, it’s as simple as running PSVersionTable on its own.

    -- Check PowerShell Version
    $PSVersionTable
    PSVersionTable

    I’m running version 5.1.19041 of Windows PowerShell here.

    At the time of writing, version 5.1 is the latest Windows PowerShell version, but not the latest PowerShell version available to us. To get version 7, we can download and install it as per MS Docs: Installing PowerShell on Windows.

    Before looking at rolling out upgrades across several Windows hosts, we should look at the current Supported PowerShell Versions for Windows compatibility table from Microsoft.

    That’s it for now on this one. Cheers again.

  • How to Automate PowerShell Scripts with Task Scheduler

    How to Automate PowerShell Scripts with Task Scheduler

    This post is useful if you need to run PowerShell Scripts automatically with a set schedule in Windows. In the demos below, we’re creating new Scheduled Tasks via GUI and with PowerShell.

    Task Scheduler is a Windows application that we can use to create and schedule any task. We can set our Task Actions to start programs, send emails, and display messages, or we can run any script – likely a PowerShell script since we’re on Windows. These Actions can be triggered on any schedule you configure your Scheduled Task to use.

    As a Systems Administrator, adding a Scheduled Task in Windows is something we might need to do regularly. As a SQL Database Administrator, we’d use the SQL Server Agent to schedule PowerShell scripts and other tasks. And Linux Sysadmins, they’ll use Cron.

    This post covers the following :
    # How to Create a Scheduled Task using PowerShell (CLI Option)
    # How to Create a Scheduled Task for a PowerShell Script (GUI Option)

    How to Create Scheduled Task using PowerShell

    First, we need to create the PowerShell script that we want to be executed o a schedule. In the screenshot below, I’m creating an example .ps1 script in my c:\temp directory for this demo.

    The script below updates the content of a text file, logging the current Average CPU of the computer with date/time. You could have anything in your PowerShell script, e.g. Restart-Service *ServiceName* or Restart-Computer.

    Create PowerShell Script

    Now, I’m creating a new Scheduled Task that will run the above PowerShell script on a daily Schedule. The following script uses the New-ScheduledTask and Register-ScheduledTask cmdlets.

    $actions = (New-ScheduledTaskAction -Execute 'C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1')
    $principal = New-ScheduledTaskPrincipal -UserId 'Administrator' -RunLevel Highest
    $trigger = New-ScheduledTaskTrigger -Daily -At '8:05 AM'
    $settings = New-ScheduledTaskSettingsSet -WakeToRun
    $task = New-ScheduledTask -Action $actions -Principal $principal -Trigger $trigger -Settings $settings
    $taskPath = '\Admin\'
    # create new scheduled task as per parameters above
    Register-ScheduledTask 'DailyPSCollector' -InputObject $task -TaskPath $taskPath
    PowerShell Create New Scheduled Task

    How to Create a Scheduled Task for a PowerShell Script (GUI)

    This is the GUI way of creating Scheduled Tasks in Windows. Open the Task Scheduler application to get started, and right-click in the empty area as shown to Create New Task.

    Populate details in the General tab as shown in the screenshot below.

    Task Scheduler Create New Task

    Go to the Triggers tab to configure the desired schedule for our new job.

    Task Scheduler Triggers Tab

    Next, in the Actions Tab click to create a new task:

    Action: Start a program
    Program/script: powershell
    Arguments: -File “C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1”

    Task Scheduler Actions Tab

    Have a quick review of the Settings tab and click OK once done.

    Task Scheduler Settings Tab

    We can now review our new task in the Task Scheduler main window.

    Task Scheduler PowerShell Script

    And that’s us sorted. The PowerShell script will run daily at 6 pm as we specified during Task creation.

    Feel free to check out the Windows Admin and/or PowerShell Tips tags for more random informational blog posts.

  • SQL Server Default Port

    SQL Server Default Port

    The default port for the SQL Server Engine is 1433, which is a useful thing to know if you’re working as a Database Administrator (DBA) or Database Reliability Engineering (DBRE) type role.

    Though-out my career I’ve seen SQL Server run on the default 1433 port 99% of the time. For the other 1 percent, it didn’t matter much to me what port it was using.

    Occasionally as a DBA, you will need to arrange network line of sight (firewall rule changes) to be carried out by Network teams for MSSQL host-to-host connectivity. This is done via some request form a lot of the time, and we need to send source/destination addresses. Including IP addresses, we would also need to verify which port the SQL Server is using. Always best to double-check which MSSQL is running before submitting these types of requests, the demo below will help get this info.

    We also need to consider the local Windows Firewall on each SQL Server host. The rule should be scoped to the individual IP connecting, or subnet (10.20.30.0\24, all 256 addresses).

    SQL Server uses a lot of ports for various features & services, e.g. SQL Browser 1434 / SSIS 135. For anything beyond this basic SQL port info, MS Docs will provide you with what’s needed – there’s a lot, which I’m currently digesting at the time of writing.

    Anyway, with network stuff out of the way, this post contains the following demos:
    # How to Check Default SQL Server Port
    # How to Add New Windows Firewall Rule for Default MSSQL Port (1433)

    How to Check Default SQL Server Port

    Open SQL Server Configuration Manager, which is accessible via the Start Menu on any SQL Server host.

    Navigate as shown on the screenshot below, to SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP Properties > IP Address Tab

    SQL Server Check Default Port

    You can amend ports here too, but read MS Docs and test first if doing on Production, of course.

    How to Add New Windows Firewall Rule for Default MSSQL Port

    This is an added note on how to add the SQL Server 1433 Port & SQL Server Browser 1434 Ports. We can use the GUI >

    Windows Firewall Add New Rule

    And we can do the same with PowerShell, using the New-NetFirewallRule cmdlet.

    # Add SQL Server Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
    # Add SQL Server Browser Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

    I hope this was a useful SQL Tip. I have another post that relates to Windows Ports- How to Find Which Process is Listening on a Port in Windows

  • PowerShell Restart Services on Multiple Hosts

    PowerShell Restart Services on Multiple Hosts

    In this post, we’ll walk through a script that restarts services on multiple remote hosts using PowerShell. A single PowerShell command will be run on several computers at the same time.

    If you need to run a command on multiple remote machines, the PowerShell Invoke-Command cmdlet is a great option. We use this to send commands for execution on remote computers.

    The command being passed in this demo is restarting the SQL Server Agent Service using the Restart-Service.

    Restart Services Multiple Hosts with PowerShell

    To use the script, simply populate the ComputerName array with your hostnames and then run the script. Here’s an example:

    # Restart service on multiple hosts
    $parameters = @{
      ComputerName = 
      	"Hostname1",
      	"Hostname2",
      	"Hostname3"
      ScriptBlock = { Restart-Service *SQLSERVERAGENT* }
    }
    Invoke-Command @parameters
    PowerShell Restart-Service

    The ScriptBlock argument can be amended to run a different PowerShell command, or you can add error checking if you’re building something more complex.

    Remember that you should always be careful when running scripts that modify services on remote hosts. It’s a good idea to test the script on a single host before running it on multiple hosts to ensure that it works as expected.

    As always, hope to follow this up with more of the same random blog posts. Feel free to check out my other PowerShell Tips.

  • PowerShell ForEach Loop Tutorial

    PowerShell ForEach Loop Tutorial

    Looping is a fundamental concept in PowerShell, and in programming in general. It’s needed for various situations where we need to work with one object at a time within an array/collection of objects.

    Microsoft’s documentation on this, about Foreach : Microsoft Documentation, describes Foreach to be for ‘stepping through (iterating) a series of values in a collection of items‘ – always appreciate a good technical description.

    This post contains some basic examples of the ForEach Loop in PowerShell:
    # Basic ForEach Statement
    # ForEach with Get-ChildItem

    Basic ForEach Statement

    We can hardcode anything into this array below, or populate it from somewhere else.

    This is one of the most basic examples as shown in MS Docs, we’re iterating through each letter in the letterArray, writing to the terminal the value foreach loop.

    $letterArray = "a","b","c","d"
    foreach ($letter in $letterArray)
    {
      Write-Host $letter
    }
    PowerShell ForEach

    ForEach with Get-ChildItem

    The PowerShell script below performs a write-to Console for each file in the demoFolder Directory.

    I’m running this twice, the second time navigating out of the demoFolder.

    foreach ($file in Get-ChildItem)
    {
        Write-Host $file
        Write-Host $file.length
        Write-Host $file.lastaccesstime
    }
    PowerShell ForEach Get-ChildItem

    The above is also showing us the LastAccessTime Property for each file.