Press "Enter" to skip to content

Category: Backups

The Importance of backup_label Files for Postgres

Published 2023-05-26 by Kevin Feasel

Robert Haas says, don’t delete that file:

I’m sure you already know what I’m going to tell you: “Of course you need that backup_label file. How could you even think that you don’t need that backup_label file?” Well, you’re right. That is what I’m going to say. But do you know why you need that backup_label file? If you were to remove that backup_label file (or fail to create in the first place, in cases where that is your responsibility), what exactly is the bad thing that would happen to you?

Read on to learn what the file does, how you get it, and why it’s so important to keep around.

Leave a Comment

Combining Backup Encryption and Compression

Published 2023-05-25 by Kevin Feasel

Matthew McGiffen joins two great flavors:

In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.

Read on for more information. Microsoft did the right thing: they compress first and then encrypt; otherwise, you’re not getting any benefit from the compression.

Leave a Comment

Notes on Postgres Backups

Published 2023-05-23 by Kevin Feasel

Muhammad Ali hits us with it:

Backing up your PostgreSQL database is a critical task for ensuring the safety and availability of your data. In the event of a hardware failure, software error, or other disaster, having a recent backup of your database can mean the difference between a brief outage and a catastrophic data loss. In this blog post, we’ll cover best practices for backing up PostgreSQL database.

Click through for some notes on various backup utilities (pg_dump, pg_dumpall, pg_basebackup), when you might want to use each, and a few more topics.

Leave a Comment

Encrypting SQL Server Backups

Published 2023-05-18 by Kevin Feasel

Matthew McGiffen lays out the requirements:

When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.

From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.

Click through for a primer on the topic.

Leave a Comment

TDE and Database Backups

Published 2023-05-16 by Kevin Feasel

Matthew McGiffen shares some advice:

Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.

Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.

Leave a Comment

Which Backups are in a File?

Published 2023-05-11 by Kevin Feasel

Steve Jones didn’t keep ’em separated:

I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.

Note: Don’t do this. Put backups in separate files.

Still, if you didn’t follow Steve’s good advice here (or you have an edge case situation where you, for some reason, need to store multiple backups in the same file), there’s a way to check what’s in a file.

Comments closed

Point-in-Time Restoration with pg_basebackup

Published 2023-04-04 by Kevin Feasel

Matt Pearson and Luke Davies restore a PostgreSQL database:

I had a conversation with another DBA about interview questions, and one interview topic that came up was using pg_basebackup to restore a database. I had a horrible realisation that I had not done a restore using pg_basebackup in PostgreSQL 15. With modern backup tools, using pg_basebackup is like using a manual screwdriver instead of an electrically-powered screwdriver; it gets the job done, but much more effort is involved.

However, sometimes pg_basebackup is the only tool available.

So, in this blog, we’ll look at PostgreSQL’s recovery options and their implications for restoring. We’ll also look at a simple restore using pg_basebackup when a user fails. I’ll be using a PG 15 database for these tests.

Click through for the process and a demonstration.

Comments closed

Accelerated Database Recovery in SQL Server 2022

Published 2023-03-29 by Kevin Feasel

Perry Skountrianos takes us through some recent changes:

In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.

MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance.

Read on to see how you can change that, as well as additional product updates.

Comments closed