Press "Enter" to skip to content

Category: Backups

Incremental Backup in Postgres

Published 2024-01-05 by Kevin Feasel

Robert Haas talks about a new feature:

Five days before Christmas I committed my patch to add incremental backup to PostgreSQL. Actually, I’ve been committing preparatory patches for some months now, but December 20 saw the two main patches land. Since then, there’s been a bunch of bug-fix commits, and there are still a few pending items that need to be addressed, but the core of the feature is now committed. If you want a quick overview of the feature, Lukas Fittl has a great video about that. Here, I’d like to talk about the architecture of the feature itself in a little more detail, and specifically with how we decide which data to copy.

Most people who are likely to read this blog are probably already familiar with the core idea of an incremental backup: instead of copying the whole database instance, just copy the data that has changed. That’s faster, and takes up less space on disk. But, to work properly, you have to be able to quickly and reliably identify which data has, in fact, changed. There’s more than one way to do that.

Read on for some of the complexity around this. It’s interesting to see what goes on behind the scenes in a relational database.

Comments closed

Backup and Recovery Options for Relational Databases

Published 2023-12-14 by Kevin Feasel

Adron Hall keeps a copy:

In the realm of data management, ensuring the safety and recoverability of data is paramount. Relational databases, being at the core of many business operations, require robust backup and recovery strategies. This article delves into the general concepts of backup and recovery in relational databases, followed by specific strategies for SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL.

Click through for a high-level overview of general database backup concepts and a variety of options available in the major relational database platforms.

Comments closed

Failure Writing Backups to Azure Blob Storage Due to Limits Reached

Published 2023-11-29 by Kevin Feasel

David Fowler hits a wall:

Picture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error…

Write to backup block blob device https://****** failed. Device has reached its limit of allowed blocks.

What’s going on, nothing’s changed?!

Read on to learn the cause of this issue as well as three ways to fix it.

Comments closed

I/O Freeze in SQL Server 2022 T-SQL Snapshot Backups

Published 2023-11-24 by Kevin Feasel

Anthony Nocentino has a public service announcement:

SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.

When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.

Read on to understand what’s going on, what “frozen” really means, and why this is a huge improvement over the classic behavior of the volume snapshot service.

Comments closed

Ensure Azure Backup Retention Periods via Azure Policy

Published 2023-10-30 by Kevin Feasel

Sabrin Alsahsah keeps backups around:

In this blog article, we will cover how to prevent and deny the automated backup retention days for Azure SQL database to be lower than X number of days.

We have received some service requests from customers that would like to ensure that they have for example 14 days as a retention period for their automated backup and would like to do this using a custom policy since they have multiple databases.

You can follow the steps below to create a custom policy and ensure this:

Click through for that process.

Comments closed

The Internals of Backup Compression

Published 2023-10-10 by Kevin Feasel

Andy Yun continues a series on how backups work in SQL Server:

Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.

When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).

This post taught me a few things about the practical impact of enabling compression. Even after reading this, however, I would almost always enable it for two reasons. First, storage is usually the bottleneck for organizations, so actions which reduce storage utilization can improve overall performance. Second, there are limits to how much we can store, so compressing backups may let me get away with holding more backups on a given LUN or drive.

Comments closed

Verifying a Backup in SQL Server

Published 2023-09-20 by Kevin Feasel

Chad Callihan knows your last backup is only as good as your last restore:

Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.

There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.

Click through to learn more about this. And you could easily put together Powershell scripts to stagger your restorations over a time frame (such as, 15% of your databases each day, so that you get to 100% by the end of the week).

Comments closed

Restoring Backups from S3 to Azure SQL MI

Published 2023-09-06 by Kevin Feasel

Strahinja Rodic announces a new feature going to GA:

In September last year SQL server 2022 introduced new feature – backup and restore to simple storage service (S3) – compatible object storage that grants the user the capability to back up or restore their databases using S3-compatible object storage, whether that be on-premises, or in the cloud.

To provide this integration Azure SQL MI is enriched with a new S3 connector, which uses the S3 REST API to connect to Amazon S3 storage. It extends the existing RESTORE FROM URL syntax by adding support for the new S3 connector using the REST API.

Click through to see what you need to have set up for it to work, as well as the restoration process.

Comments closed