Press "Enter" to skip to content

Category: Backups

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.

Leave a Comment

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

Restoring a Database in Three Separate Ways

Published 2024-11-06 by Kevin Feasel

I have a new video:

In this video, I show how to restore SQL Server databases using SQL Server Management Studio, T-SQL, and the dbatools PowerShell module.

Originally, I had plans on covering SSMS + T-SQL in one video, and then dbatools in a second. Then I decided, well, why not just cover both of them in the same video? And this is how we get to half-hour long videos.

Comments closed

The Power of pg_dump

Published 2024-11-04 by Kevin Feasel

Robert Haas talks up pg_dump:

I wrote a blog post a couple of weeks ago entitled Is pg_dump a Backup Tool?. In that post, I argued in the affirmative, but also said that it’s probably shouldn’t be your primary backup mechanism. For that, you probably shouldn’t directly use anything that is included in PostgreSQL itself, but rather a well-maintained third-party backup tool such as barman or pgbackrest. But today, I want to talk a little more about why I believe that pg_dump is both amazingly useful for solving all kinds of PostgreSQL-related problems and also just a great piece of technology.

The core value proposition of pg_dump is that the output is human-readable text. You’ll get DDL commands that you can use to recreate your database objects, and you’ll get COPY commands (or INSERTs, if you so request) that you can use to reload your table data. That is not really an advantage if you’re just trying to back up and restore an entire database cluster, because converting all of your data from PostgreSQL’s internal formats into text and back again is going to use a bunch of CPU resources. If you instead take and restore a physical backup, you can avoid all of that overhead.

Read on for scenarios in which pg_dump can be quite useful.

Comments closed

Backing up SQL Server via PowerShell

Published 2024-10-30 by Kevin Feasel

I have a new video:

In this video, I show how to perform a variety of database backup operations via PowerShell, using the dbatools PowerShell module. I also show how easy it is to test a database backup using dbatools.

I finally have a video shorter than 10 minutes long. Don’t worry, the next one will blow right past that figure.

Comments closed