Peter Whyte - SQL Server DBA Blog

SQL DBA Blog

How to Change Your Default Distro in WSL
Changing the default Windows Subsystem for Linux (WSL) distribution is simple. This guide shows you how to list your installed distros and set a new default using just a couple of commands.

1. Listing Installed WSL Distributions

Before setting a new default, check which distributions are installed on your system. Open a terminal and run:
```
# show installed wsl distros
wsl -l -v
```
This command lists all installed WSL distributions along with their version numbers. Take note of the exact name of the distribution you want to set as the default.

2. Changing the Default WSL Distro

Once you have identified the distro you want to use as the default, run the following command:
```
# set default wsl distro
wsl -s <wsl-distribution-name>
```
Replace <distro-name> with the name of your preferred default distribution.

In my example above, I’m setting Debian as the default distro and then when I enter that WSL Debian, I’m running cat /etc/os-release to verify what Linux flavor I’m running.

That’s it, you’ve now set your default WSL distribution! For more random WSL tips, feel free to check out my other WSL Blog posts.
July 25, 2022
Get Last Database Restore Date and Time in SQL Server

This guide provides a script to retrieve the last restore date and time for each database in SQL Server. It’s particularly helpful for tracking restore operations or verifying backups during disaster recovery scenarios.

The script below queries the sys.databases and dbo.restorehistory system tables to get the most recent restore details for each database.

If a database wasn’t created via a restore (e.g., created manually or during SQL Server installation), the restore_date column will return NULL. In such cases, this script may not provide meaningful results.

Here’s the SQL script:

Restore Types: The restore_type column indicates the restore operation type:
> D: Full backup restore
> L: Transaction log restore
> I: Differential restore
Refer to MS Docs for detailed column descriptions.

For more tips on SQL Server backups from an experienced SQL DBA, checkout my database backup & recovery tag!

July 23, 2022
List All SQL Agent Jobs in SQL Server

SQL Server Agent jobs are needed for automating tasks such as backups, index maintenance, and data processing.

This post provides a SQL script to list all SQL Server Agent jobs on an instance, along with useful tips to help understanding.

How to List SQL Agent jobs in SQL Server

The following query retrieves all SQL Agent jobs, including job steps and key details:

This query provides a clear view of SQL Agent jobs, including their status, execution history, job steps, and scheduling details. You can quickly determine if a job is active, when it last ran, how long it took, and whether it succeeded or failed. Additionally, it reveals the steps within each job, their execution context, and any retry settings. Scheduling details help you understand when and how often a job is set to run.

✅ Key Details
– Job Overview: Name, description, status (enabled/disabled), creation date, and last modified date.
– Execution History: Last run date/time, run outcome (success/failure), and execution duration.
– Job Steps: Step name, database context, command, and retry behavior.
– Schedule Information: Schedule name, frequency, interval, and start time.

📖 For more details, refer to MS Docs:
📌 sysjobs | 📌 sysjobsteps | 📌 sysschedules

Retrieve Only SQL Agent Jobs (Excluding Steps)

If you only need a list of jobs without step details, simply query the table as linked above:

This returns one row per job, helping you quickly review job configurations.

Tips for Managing SQL Agent Jobs

Here’s how you can make your SQL Agent job management easier:

> Use Descriptive Names & Tags:
Clearly name jobs to reflect their purpose and consider using standardized prefixes (e.g., DBA_, ETL_, Backup_) to improve job organization. Add detailed descriptions where appropriate.

> Monitor & Alert on Failures:
Regularly check for failed executions to ensure reliability. Set up Database Mail (DBMail) or a customized alerting solution to notify DBAs instantly when a job fails.

> Optimize Scheduling & Performance:
Avoid scheduling multiple resource-intensive jobs simultaneously. Use job history’s run_duration to analyze and optimize job execution times, preventing overlaps and performance bottlenecks.

> Backup Job Configurations:
Before making changes, export job details using sp_help_job, PowerShell, or custom scripts to ensure you can quickly restore them if needed.

Hope all this was useful for you!

July 21, 2022
How to Check WSL Distro Versions
This is a quick post on how to check your Windows Subsystem for Linux (WSL) distribution versions, with added info on what being on a different WSL version means.

To check your WSL version we can simply run wsl -l -v in our PowerShell terminal window. This will show all installed WSL Linux distro names and versions.

What is WSL?

Windows Subsystem for Linux allows users to develop and run applications within a GNU/Linux environment on a Windows computer. Previous to this feature release we needed Virtual Machines (VMs), and dual boot setups were much more common if you wanted local test environments.

WSL Version 1 vs 2?

There are 2 versions of WSL, the old (WSL1), and the new (WSL2). We should ideally be using the latest version like all other software we use in life, however there are some use-cases where we might prefer to use WSL Version 1.

The image below shows the current feature differences between WSL v1 and WSL v2:

Have a look at MS Docs: Comparing WSL 1 and WSL 2 for more information on WSL version differences. One of the key differences in 2022 is that WSL 2 allows Docker.

Checking WSL Distro Versions

To check the versions of your installed WSL distros, open your PowerShell terminal and run the following:
```
# wsl check version
wsl -l -v
```
The command above lists all installed Windows Subsystem Linux distributions on your local machine, including WSL versions (WSL1 or WSL2) and their state (Running or Stopped).

Other Tips

I’m using Windows Terminal in this demo, which is great for managing multiple WSL distros. We are able to open multiple WSL distros as tabs separate tabs as shown in the screenshot above.

That concludes this post. Feel free to check out some of my other WSL blog posts using the WSL tag link below.
July 20, 2022
How to Restore a Database in SQL Server
Restoring a database in SQL Server is an essential task for Database Administrators (DBA’s). We need to be able to quickly restore databases as part of Disaster Recovery (DR), as well as for configuring SQL features such as Always On or Database Mirroring.

You can restore databases using the SQL Server Management Studio (SSMS) GUI or by executing SQL commands. Personally, I often use the SSMS Restore Database Wizard to generate a script, which I then run in a query window. This approach is especially helpful when dealing with databases that have multiple data files spread across different drives.

Topics Covered:
> Restoring a SQL Server Database using Command
> Restoring a SQL Server Database with Multiple Files
> Verifying Database Restores
> Further Reading

Restoring a SQL Server Database using Command

For this demo, first I’m going to perform a Full Database Backup on my test database.
```
-- Perform full database backup to local temp directory with compression
BACKUP DATABASE [lemonadestand] TO DISK = N'c:\temp\lemonadestand_full_11082022.bak' WITH COMPRESSION;
```
This creates a compressed backup file in C:\temp.

We’re going to simulate the need for a restore by dropping the database.
```
-- Drop the test database
DROP DATABASE [lemonadestand];
```
Now that the database is removed, we can proceed with the restore.

Restore Database Command:
```
-- Restore test database we just dropped
RESTORE DATABASE [lemonadestand] FROM  DISK = N'C:\TEMP\lemonadestand_full_11082022.bak';
```
After executing the restore command, my test database becomes fully operational again.

Restoring a SQL Server Database with Multiple Files

In cases where backup performance needs to be optimized or disk space needs to be managed efficiently, database backups can be split across multiple files. Here’s how to restore a database from multiple backup files.

Restore Database from Multiple Files:
```
-- Restore the lemonadestand database
RESTORE DATABASE [lemonadestand]
FROM DISK = N'C:\TEMP\lemonadestand_full_11082022_part1.bak', 
     DISK = N'c:\temp\lemonadestand_full_11082022_part2';
```
By specifying multiple backup files, SQL Server will automatically read from all files and restore the database accordingly.

Verifying Database Restores

Once the restore process completes, we can verify that the database is online and accessible simply by refreshing the databases in SSMS Object Explorer:

Alternatively you can check the status of databases in SQL Server by running the following query:
```
-- check database states
SELECT name, state_desc, * FROM sys.databases WHERE name = 'lemonadestand';
```
While reviewing this table we can check the database state as well as configuration settings are set correctly, such as the database owner.

Further Reading

I have more blog posts relating to restoring databases in SQL Server, including the following:

SQL Script: Get Last Database Restore Time
Check when a Database was last restored using the SQL Script provided in this post.

SQL Script: Get Estimated Database Restore Time
A SQL Script to give an estimate for how long a db restore will take, useful for restoring large databases that take hours or days to complete.

Why is the Database in “In Recovery” Mode?
Helping you resolve issues if your SQL Server Database is stuck “in Recovery” Mode.

Database Cannot be Opened as it’s in the middle of a Restore
Again, helping with another common error you might encounter while restoring databases in SQL Server.

I hope this guide was a useful one for you! For more information on this, checkout the Microsoft Docs on Restoring Databases in SQL Server.
July 19, 2022