Press "Enter" to skip to content

Category: Backups

Using MIRROR TO in a Backup Operation

Published 2025-02-20 by Kevin Feasel

Andy Yun tries out an option:

About a month ago, was having a conversation with a Pure Storage customer’s SQL Server backups. They were using Ola Hallengren’s scripts but mentioned something about writing the backups to multiple locations at once. I presumed they were executing the backup operation as normal, then using another script of some sort to then copy the backup files elsewhere. That’s when I learned that Ola has a parameter called MirrorDirectory, that exposes the MIRROR TO option in a native BACKUP command.

Click through to learn more about this command and the implications. I’d say that this sounds neat but also isn’t something I’d probably recommend.

Leave a Comment

Backup to URL via Managed Identity in SQL Server 2022

Published 2025-02-18 by Kevin Feasel

Joey D’Antoni doesn’t trust user logins:

Backing up databases to the cloud is not a new thing. Microsoft introduced the BACKUP TO URL functionality to SQL Server 2012 SP1 CU2. I’m not going to tell you how long ago. Still, it wasn’t last month, and Microsoft recently celebrated the 15th anniversary of Azure so that you can get an idea. When the feature started—it was minimal; you could only backup a database of up to a single terabyte and couldn’t stripe over multiple files. Additionally, you had to use the access key to the storage account, which gave complete control over the storage account—that wasn’t a good thing.

Read on for a quick overview of the feature and guidance on how it all works.

Leave a Comment

Multi-Storage Array Database Snapshots in SQL Server

Published 2025-02-03 by Kevin Feasel

Anthony Nocentino continues a series:

In this post, the fourth in our series, I want to share an example demonstrating SQL Server 2022’s T-SQL Snapshot Backup feature in a scenario where a database spans multiple storage arrays. If you’re dealing with multi-array environments, you’ll appreciate how this technique freezes database write I/O to take coordinated snapshots across volumes on two FlashArrays. In this post, I’ll walk you through the process, point out some of the script’s key elements, and show you how long the write I/O pause takes.

Click through to learn how it all works when you have a database spanning multiple volumes.

Leave a Comment

Automating DBCC CHECKDB on Full Backups

Published 2025-01-15 by Kevin Feasel

Shane O’Neill riffs off of a script:

Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.

At least this way we can test the backups we take can be restored, as well.

But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…

Click through for a script that Shane built off of a Madeira Data Solutions script that Eitan Blumin put together.

Comments closed

Automating Backups with Minion Backup

Published 2024-12-18 by Kevin Feasel

I have a new video:

In this video, we will learn how to use Minion Backup to automate backup operations, including setting backup frequency, dynamic backup tuning, and scripting out database restorations.

This video wraps up a series on backup and recovery in SQL Server. Now I have to come up with some new videos.

Comments closed

Recovering SQL Server Data via FlashArray Snapshot

Published 2024-12-17 by Kevin Feasel

Andy Yun does a bit of data recovery:

Almost all of us have experienced a time when someone incorrectly modifies or deletes a subset of data in a database. And usually the only recourse is to run a database restore to get the data that we need back.

But… what if we cannot simply run a RESTORE operation over the entire database. Maybe the accident occurred a few hours prior and you cannot roll everything back. You need to do a side-by-side RESTORE.

But… what if the database in question is absolutely massive and will take hours to restore? And what if your server doesn’t have enough available storage to fit a second copy of that database at all?

Andy has answers to these questions. Granted, they require some expensive (and very good) hardware and a proper configuration of said hardware, but it does work beautifully.

Comments closed

Filegroup Backup and Restoration in SQL Server

Published 2024-12-11 by Kevin Feasel

I have a new video:

In this video, I show how to back up and restore SQL Server databases in piecemeal form, using filegroups to manage read-only versus read-write data, and bringing the most important data back online sooner in a recovery scenario.

I found the process to be a bit trickier than I had first expected, so I’m hoping this video has enough legs to prevent others from running into some of the problems I experienced.

Comments closed

Backing up SQL Server to S3 and Blob Storage

Published 2024-12-04 by Kevin Feasel

I have a new video:

In this video, I show how to back up a database directly to AWS S3-compatible storage (in SQL Server 2022) and Azure Blob Storage.

For the S3 portion, I actually use MinIO because I didn’t want to spend several pennies making this video. But it does turn out that the mechanisms are the same as how you would work in AWS S3, once you have the URL and access key.

Comments closed