Press "Enter" to skip to content

Category: Backups

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

Restoring a Database with Standby in SQL Server

Published 2023-08-31 by Kevin Feasel

Steve Jones stands by for station identification:

Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.

Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.

Click through to see how you can do that.

Comments closed

CPU Threads in SQL Server Backups

Published 2023-08-25 by Kevin Feasel

Andy Yun dives in:

Welcome back to Part 3 of my SQL Server Backup Internals Series.

In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.

Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.

Comments closed