A Fine Slice Of SQL Server
Marlon Ribunal lays out a case:
After sharing my thoughts on SQL Server native backups using Ola Hallengren’s Maintenance Solution, Mark Gribler left a comment that caught my attention:
“The proof of a backup is a restore” (apologies to puddings)!!
I had to smile at that one.
By the way, I give Marlon +1 point for getting the aphorism correct. The correct aphorism is “The proof of the pudding is in the eating.” Ponder that while checking those backups.
Leave a CommentWarwick Rudd tries to make a change:
In Part 1 we built an immutable container. In Part 2 we gave the SQL Server 2025 VM a Managed Identity and Microsoft Entra authentication. This is where it comes together. We’ll keep the traffic between SQL Server and storage on the private network, lock down who can reach the storage account, give the VM permission to write, and then take a backup and try to delete it.
Read on for those steps.
Leave a CommentWarwick Rudd continues a series:
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.
Comments closedWarwick 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.
Comments closedStefan 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 witharchive-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=nmight allow the standby-based approach.
Read on for that answer, as well as some of the issues you might run into.
Comments closedOne 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 closedDrew 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 closedSean 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 closedGarry 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 closedHans-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