SQL DBA Blog

  • PowerShell For Each Loop Example

    PowerShell For Each Loop Example

    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 , describes it as ‘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, including the following:
    > Basic PowerShell For Each Example
    > ForEach with Get-ChildItem

    Basic PowerShell For Each Example

    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 example

    PowerShell ForEach with Get-ChildItem Example

    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 with Get-ChildItem

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

    Hope this was useful!

  • How to Check Listening Ports on Windows

    How to Check Listening Ports on Windows

    This guide will show you how to check which service is running on a specific TCP or UDP port on a Windows computer. This can be useful for verifying which service is tied to a port or proactively checking for any dodgy (suspicious) open ports.

    In this post, I’ll demo two ways to check the connection and listening port information on Windows:
    > Show Listening Ports in Resource Monitor (easy GUI option)
    > TCPView, Sysinternals Application (quick & small download)

    Show Listening Ports in Resource Monitor

    Resource Monitor allows you to view detailed listings of all TCP and UDP endpoints on your machine. It’s a simple, built-in tool for checking network activity.

    We can open Resource Monitor by typing resmon.exe in the run dialog (Win + R), or search for it in the Start menu. Alternatively, open Task Manager and go to the Performance Tab.

    Open Resource Monitor

    If using Resource Monitor, open the Network tab and expand Listening Ports to see all active TCP and UDP connections:

    Resource Monitor Listening Ports

    TCPView, Sysinternals Application

    TCPView is a lightweight tool from Sysinternals that shows all TCP and UDP endpoints on your system. It’s a great option if you need more detailed information and features such as filtering and search functionality.

    This might be the best option if you’re going to be spending some time reviewing this information as it has filtering and search functionality.

    TCPView ->

    This is what TCPView looks like:

    TCPView

    As ever, I hope this article was a useful one for those who land here and get this far!

    Sysinternals has a bunch of tools for Windows Admin, you should check that out see what’s most popular and on offer if keen.

  • Get Database Sizes in SQL Server

    Get Database Sizes in SQL Server

    Knowing how much disk space your databases are consuming in SQL Server is an important thing to know and monitor as a Database Administrator. As well as this, we should be looking at future growth of database files, ensuring we have enough disk space for years to come.

    It takes more than a few SQL scripts in a blog post to get to know your disk space requirements of a SQL Server. The scripts I provide here do help though, and give a good quick glance at database sizes, including how full the data files are.

    There’s many ways to show database sizes in SQL Server. As a DBA, it’s always great to have SQL scripts to get this info which I share below.

    SQL Script to Show Database Sizes in SQL Server

    The script below will return all databases sizes (MB & GB) for a SQL Server instance:

    Get Database Sizes SQL Server

    I often bring up the Shrink File window in SSMS as shown above to view the data and log file sizes. From this window we can easily check how full our database file(s) and log files are. .

  • How to Backup a Database in SQL Server

    How to Backup a Database in SQL Server

    In this blog post I’m sharing a demo for how to backup databases in Microsoft SQL Server.

    As a Production SQL DBA, having backups of your data is just as important as keeping your SQL Server online/available to serve its applications and users. If you don’t have confidence you have backups of data, then it’s your job to investigate & resolve it. You never want to be in a position where you’ve lost business critical data, obviously.

    Topics Covered:
    1. SQL Server Full Database Backups
    2. SQL Server Transaction Log Backups
    3. SQL Server Differential Backups
    4. Additional Notes / Conclusion

    Before performing changes or running commands on a Production SQL Server, we should always refer to Microsoft’s official documentation, here’s their Database Backups Overview page.

    1. SQL Server Full Database Backups

    A Full Database Backup in SQL Server captures the entire database, including all the data up to the time the backup is taken. It serves as your primary restore point.

    Before starting, make sure there’s enough space on the disk where the backup will be stored. If you use the compression option, you’ll save a lot of space – we can calculate the compression ratio of a backup to help with estimating disk space requirements for VLDBs (very large databases).

    To perform a Full Database Backup with Compression, run the following:
    Amend the database name and change the backup location to your directory. 

    -- Run full database backup with compression
BACKUP DATABASE [demoBlog] TO DISK = N'd:\D:\mssql_backups\demoBlog_full_01082022_1300.bak' WITH COMPRESSION, STATS;
    SQL Server Full Database Backup

    Note: The time it takes for a backup to complete on the database size and other factors. For large databases, backups can take several hours or even days to complete if not optimized. Consider using a SQL Script to show how long a backup has left to complete.

    To keep your important servers secure from data loss, we should have a scheduled job to run Full Database Backups on our SQL Server. Having Full Backups run daily is a common approach, followed by a clean-up job to remove backups older than 30 days.

    2. SQL Server Transaction Log Backups

    Transaction Log (TLog) Backups in SQL Server records the changes made to the database since its last Full Backup. TLog backups are needed for achieving point in time database recovery, which is an essential part of having proper Disaster Recovery (DR) plans in place.

    To perform a TLog Backup, run the following: 

    -- Perform log backup for database
BACKUP LOG [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_tlog_21082022_2130.bak' WITH COMPRESSION, STATS;
    SQL Server TLog Backup Example

    Transaction Log backups need to run often, ranging from hourly to about every 10 minutes depending on your own Disaster Recovery requirements. Keeping about a week of TLog backup files would give great flexibility for recovering data loss for a specific time and day.

    On non-Production environments, you might not need to use TLog backups.

    3. SQL Server Differential Backups

    A Differential (Diff) Database Backup stores only the data that has changed since the last Full Backup, which makes it faster to run compared to a Full Backup. Differential backups are less common in production but can be useful for quick interim backups.

    Run a Diff Backup with the following command:

    -- Run differential db backup
BACKUP DATABASE [demoBlog] TO DISK = N'd:\mssql_backups\demoBlog_diff_21082022_2133.bak' WITH DIFFERENTIAL, COMPRESSION, STATS;
    SQL Diff Backup Example

    Key Point: The size of a Differential Backup depends on how much data has changed since the last Full Backup.

    4. Conclusion

    Backing up your SQL Server databases is crucial for maintaining data integrity and ensuring disaster recovery readiness.

    To optimize your backup strategy, consider these best practices:

    • Schedule Regular Backups: Implement a backup schedule that aligns with your business needs, incorporating Full, Transaction Log, and Differential backups as necessary.
    • Test Restores Frequently: Regularly verify that backups can be successfully restored in a non-production environment.
    • Monitor Backup Sizes and Durations: Track backup times and storage requirements to prevent unexpected issues.
    • Use Compression: Enable backup compression to save space and improve efficiency.
    • Run Backups During Off-Peak Hours: Schedule backups when database activity is low to minimize performance impact.
    • Back Up to Multiple Locations: Store backups in different locations (e.g., on-premises and cloud) to safeguard against hardware failures or disasters.
    • Leverage Readable Secondary Replicas: If using Availability Groups (AG) with readable secondaries, offload backup tasks to reduce the load on your primary server.

    Following these practices will help you ensure that your backup strategy is robust, efficient, and aligned with your business recovery objectives.

    Security/Permissions: When managing backups, as a Database Administrator we should be monitoring to ensure the backup chain remains intact. We achieve this by following the Principal of Least Privilege (PoLP) when we set permissions for all SQL users so that only specific users can run backup commands. We also do avoid breaking the chain with manual Full Backups by using the COPY_ONLY option.

    Additionally, always verify the last backup date before initiating a new backup. If backups are part of a scheduled disaster recovery plan, they should already be occurring at regular intervals.

  • Get Estimated Database Backup/Restore Example

    Get Estimated Database Backup/Restore Example

    When you’re waiting on a database backup or restore to complete in SQL Server, we can run the script below to get an estimated time of how long the backup has left to go –

    SQL Estimated Database Restore Time

    We have one 5GB database here currently being backed up at the time of executing this SQL DMV script. The backup progress is at 27% and is due to complete 1 minute after the backup was initiated.

    This script will also be useful for getting the full command of the currently running SQL process on your SQL Server Instance, by removing the filter (WHERE Clause) on the query.