Category: Backups

Using Managed Identities to Write Backups to Azure

Published 2026-10-16 by Kevin Feasel

In Part 1 we built somewhere safe to put the backups: a storage account with a delete lock and a container with a time-based retention policy.

The next problem is getting SQL Server to write to it without giving it a password. That’s what the Managed Identity is for. The VM gets an identity in Microsoft Entra ID, SQL Server uses it to authenticate to storage, and there’s no SAS token or storage key anywhere to steal, rotate or forget about.

There are a few moving parts to get there, and most of the time spent on this is in Entra ID and networking rather than SQL Server.

Read on to see what needs to happen.

Immutable Backups on SQL Server 2025 in Azure

Published 2026-10-02 by Kevin Feasel

Warwick Rudd takes us through some steps:

Most people know they need backups. Fewer have thought about what happens when somebody else gets to them first. Ransomware doesn’t start by encrypting your databases. It starts by finding your backups, because once those are gone you have nothing to recover to and every reason to pay. So a backup that anyone with the right credentials can delete is only a partial answer. It protects you from a failed server. It doesn’t protect you from someone who has made their way into your environment.

What you want is a backup that can’t be changed or deleted by anybody until the retention period you set has passed. Not an attacker, not a compromised admin account, and not you on a bad day. SQL Server 2025 makes this a lot more practical. Backup to URL now supports Managed Identity, so you can write backups to immutable Azure Blob Storage without handing out a SAS token or a storage key. There’s no secret sitting in a credential waiting to be found.

Click through for steps to set up the storage account.

Checking archive_mode on pgBackRest

Published 2026-09-25 by Kevin Feasel

Stefan Fercot shares some advice:

A recent question on the community channels described a difficult situation: a standby had been promoted with archive_mode=off, and restarting the new primary was something the team wanted to avoid. Could they enable archiving on a downstream standby and take a pgBackRest backup from there instead?

Their attempt failed with archive_mode must be enabled, even with archive-mode-check=n. Removing the checks from pgBackRest’s source code allowed their proof of concept to succeed, but was that enough to trust the approach?

My recommendation was to return to a supported configuration, either through a restart or a controlled switchover. But I wanted to take a closer look and see whether archive-mode-check=n might allow the standby-based approach.

Read on for that answer, as well as some of the issues you might run into.

Comments closed

Taking T-SQL Snapshot Backups on AG Secondaries

Published 2026-09-15 by Kevin Feasel

Andrew Pruski takes a backup:

One of my favourite features of SQL Server 2022 was the T-SQL Snapshot Backups. The ability to leverage the snapshot capabilities of modern storage arrays to take application consistent snapshots of our databases is a game changer for anyone dealing with very large databases and struggling to hit their RPO within their RTO.

However, application consistent snapshots require the write IO of the database to be quiesced/frozen/stunned…and (rightly so) this can make DBAs nervous.

SQL Server 2025 introduced the ability to take full and differential backups of secondary replica databases in availability groups…but what about the T-SQL Snapshot Backups? Wouldn’t it be great if we could take those on the secondary and not have to stun IO on the primary database?

But Andrew didn’t stop there, so click through to see how you can do it.

Comments closed

Bacpacs vs Dacpacs

Published 2026-09-01 by Kevin Feasel

Drew Skwiers-Koballa covers two file formats that often annoy me:

When you need to move an entire database or move the objects in a database, bacpac and dacpac files often come up because of all of the tooling options to interact with them. Bacpac and dacpac files share some core similarities as well as some major differences in how they’re used in SqlPackage and other SQL tools, but their flexibility can create a bit of confusion. In this post, we are going to discuss exactly what makes bacpac specifically important, as well as explore the options that a dacpac is capable of.

My big problem with bacpacs (and dacpacs that include data) is that, once you get to database sizes that are at all interesting, bacpacs fail to work, either because they time out in creation or in restoration. Dacpacs are quite nice for database deployments and maybe including a few reference tables, but creating a bacpac of a 100+ GB database? Best of luck.

Comments closed

T-SQL Snapshot Backups and Secondary Replicas

Published 2026-08-31 by Kevin Feasel

Sean Gallardy looks at the intersection of two SQL Server capabilities:

I was recently asked about T-SQL Snapshot backups on secondary replicas, which I hadn’t looked at for many years as it’s kind of a niche item. I was definitely surprised to find little caveats here and there, thus you get this wonderful post.

If you’re unfamiliar with T-SQL Snapshot backups, it’s a way to flush and hold changes made by IO to a database (your Freeze and Thaw entries in the errorlog) so that a stable disk image can be created and then backed up at the storage or filesystem layer. This can be an extremely fast way to create snapshots of databases that are quite large (assuming you’ve set everything up correctly) even across storage arrays (assuming it supports other options, such as ODX).

Read on to see what you have to do to get both to play nice together.

Comments closed

Estimating Disk Space from .BAK Files

Published 2026-07-20 by Kevin Feasel

Garry Bargsley runs the numbers:

Today’s post comes from one of those real-world DBA situations where someone hands you a folder full of SQL Server backup files and asks what should be a simple question:

“How much disk space do we need to restore these?”

Simple question.

Not always a simple answer.

Click through for a script that calculates how much disk space you’d need to restore a given set of backups.

Comments closed

Introducing pg_hardstorage

Published 2026-06-24 by Kevin Feasel

Hans-Jürgen Schönig announces a new open-source project:

PostgreSQL was once primarily deployed on dedicated servers and virtual machines, and now runs across managed database services, Kubernetes platforms, cloud environments, hybrid infrastructures, and everything in between. Many organizations operate several of these models simultaneously, often while supporting growing data volumes and increasingly demanding recovery requirements. As PostgreSQL deployments have evolved, so have the conversations surrounding backup and recovery.

At CYBERTEC, those conversations have been taking place across customer environments, community discussions, architectural reviews, and operational workshops for many years. The result is pg_hardstorage, a new open-source community project focused on PostgreSQL backup and recovery.

Click through for a quick overview, as well as links to the project homepage and GitHub repo.

Comments closed

Fixing Blob Lease Issues on BACKUP TO URL

Published 2026-06-05 by Kevin Feasel

Greg Low fixes a problem:

Regardless of why you are using BACKUP TO URL, one of the problems that you are likely to run into at some point is the dreaded:

Msg 3271, Level 16, State 1, Line 60
A nonrecoverable I/O error occurred on file
“ https://somestorageaccount.blob.core.windows.net/backups/somedatabase.bak : “
Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (412) There is currently a lease on the blob and no lease ID was specified in the request…
Msg 3013, Level 16, State 1, Line 60
BACKUP DATABASE is terminating abnormally.

Click through to see when you might get this problem and how you can fix it.

Comments closed

T-SQL Snapshot Backups on Hyper-V

Published 2026-05-11 by Kevin Feasel

Anthony Nocentino takes a backup:

If you’ve been following my T-SQL Snapshot Backup series, you’ve seen this technique work on bare-metal and standard VM deployments where database files live on volumes directly presented to the SQL Server OS. In this post, I’m bringing T-SQL Snapshot Backup into a Hyper-V cluster environment, with database files on VHDXs backed by a Pure Storage FlashArray Cluster Shared Volume (CSV). Hyper-V adds a few extra layers to manage at the hypervisor level, but the SQL Server side of the story is identical. Let’s walk through it.

Click through to see how it all works. It has taken me a long time to accept the idea that storage volume snapshots could fit the bill, and it’s been people like Anthony, Andy Yun, and Mark Wilkinson who have allayed my concerns over the years.

Comments closed