Press "Enter" to skip to content

Category: Backups

Performance Testing ZSTD Compression for SQL Server Backups

Published 2025-05-27 by Kevin Feasel

Andy Yun tries out some backup compression:

SQL Server 2025 Public Preview is not even a week old, but I’m impressed with another new capability that was released – a new backup compression algorithm: ZSTD. This one came as a surprise, despite being part of Private Preview, as it was only released with Public Preview.

Click through for Andy’s findings. It’s just one database that is not representative of normal SQL Server databases, but it’s an interesting data point that we can use.

Leave a Comment

T-SQL Snapshot Backups to FlashArray

Published 2025-05-21 by Kevin Feasel

Anthony Nocentino cuts out the middleman:

In this post, I’ll walk you through a T-SQL script that creates application-consistent snapshots on Pure Storage FlashArray, all from within SQL Server, no external tooling. SQL Server 2025 introduces a powerful new feature: the sp_invoke_external_rest_endpoint stored procedure. This enhancement makes calling REST APIs directly from T-SQL easier than ever. Combining this new capability with Pure Storage’s API allows us to orchestrate snapshot operations seamlessly, with no external tools or scripts required.

Click through for the process. I know that sp_invoke_external_rest_endpoint will be controversial for DBAs. That’s why I think it’s good to have examples of how it can be useful before the knee-jerk reaction of “this is automatically bad” takes over.

Leave a Comment

Restoring Multiple Differential Backup Files

Published 2025-05-08 by Kevin Feasel

Tim Radney violates Betteridge’s Law of Headlines:

I was recently asked if you can restore multiple differential backups in preparation for a migration. I responded that yes, technically you can restore multiple differential backups, however it will not speed up your cutover.

As soon as I read the first sentence, the answer in my head was “Yes, but why?” Tim explains the person’s reasoning and then demonstrates that this reasoning doesn’t quite work.

Leave a Comment

Restoring Databases using Change Data Capture

Published 2025-04-28 by Kevin Feasel

Tim Radney restores a database:

I have recently had the privilege of working with multiple clients who have been taking advantage of Change Data Capture “CDC”. Change Data Capture is a feature that utilizes SQL Server Agent to log inserts, updates, and deletes occurring in a table. It makes these data changes available to be consumed in a relational format. These changes are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables.

Change Data Capture is very often utilized for ETL projects or for data extracts into a data warehouse, data lake, etc.

Click through for the scenario and how you can keep CDC going after restoring a SQL Server database.

Comments closed

Incremental Backups in PostgreSQL 17

Published 2025-04-28 by Kevin Feasel

Dave Stokes takes a backup:

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.

This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.

Click through to see how it all works. But also please test your backups, because if I were to modify the adage, I’d say that a DBA is only as good as his last tested backup.

Comments closed

Securing SQL Server Backups to Azure via Managed Identity

Published 2025-04-24 by Kevin Feasel

Arun Sirpal doesn’t want to generate a SAS token:

I do believe most people know about the ability to backup your SQL server databases to URL (from Azure VMs).  If you recall you would use the storage key ( ideally not) or a SAS token, from there you would create a SQL credential that is bound to the SAS token (as a secret). Lots of moving parts especially when it comes to rotation time. So now, if you are on the newer versions of SQL server (SQL Server 2022 Cumulative Update 17 ) start thinking about backups via managed identities.

Click through to learn how and why.

Comments closed

ReplaceInName in dbatools’ Backup-DbaDatabase

Published 2025-04-21 by Kevin Feasel

Jess Pomfret discovers a new flag:

Recently I was reading the docs for `Backup-DbaDatabase` and found a parameter I didn’t realise existed, but is so useful when you want to automate backups, but keep control of the file names.

Click through to learn more about the specific feature, as well as a reminder that it’s a good idea to read through the documentation. Not all documentation is good, but the work people have put into dbatools means that there is often a good example involving most of the available parameters. It turns out that there are, in fact, two examples that use -ReplaceInName in the documentation for Backup-DbaDatabase, so you get not only a description of the parameter but also two specific examples of how to use it.

Comments closed

Estimating SQL Server Backup Sizes

Published 2025-04-14 by Kevin Feasel

Rebecca Lewis gives us a rule of thumb:

How big will my backup file be?  Very good question.  And of course, it depends… especially if you’re using compression.

I was working something today that required a backup beforehand for safety measures, and the server had limited storage.  So, I needed to estimate the backup file size beforehand to confirm whether I had enough space for the bak file.

Click through for the script. It does, of course, include some simplifications and your actual numbers can turn out a bit different, but for a quick and dirty estimate of disk space needed, this isn’t bad at all.

Comments closed

Backups Aren’t Enough

Published 2025-04-11 by Kevin Feasel

Kevin Hill lays out a common but very important argument:

Many IT leaders and system admins think, “We have full backups every night. We’re covered.” But when the time comes to restore, they discover:

· The backup file is corrupt.
· The storage location is inaccessible.
· The restore process takes way longer than expected.
· The recovery model wasn’t configured properly.
· The point-in-time restore doesn’t actually bring back the data they need.

At that point, it’s not a “backup strategy.” It’s a data loss incident.

The solution is to test those backups, and Kevin provides some guidance on how, as well as additional important parts of the story.

Comments closed