Press "Enter" to skip to content

Category: Backups

Cannot Open Backup Device with SQL Managed Instance and SAS Token

Sam Garth troubleshoots an issue:

On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.

An exception occurred while executing a Transact-SQL statement or batch.

Additional information:
Cannot open backup device
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

Read on for the troubleshooting steps Sam followed to solve the problem.

Leave a Comment

Comparing pg_basebackup Compression Settings

Kaarel Moppel puts on the lab coat and safety glasses:

In my last post I did a quick check on the performance of the newer (lz4, zstd) pg_dump compression options, which included setting up a small framework to download some openly available “real life”-ish sample datasets. And the general result was that, indeed – the new algos in lower levels provide the best value, especially zstd.

But pg_dump is about compressing essentially text based data…but how about binary Postgres data? Thus the tool to test here additionally is pg_basebackup, with its newer (v15+) compression options. So let’s see if something stands out consistently again.

Click through for the test results.

Comments closed

Restorable Dropped Databases Naming in Azure SQL DB

Tanayankar Chakraborty asks, what’s in a name?:

An issue was reported recently where the customer complained that in their cost analysis report of their Azure SQL DBs, the db name appears appended with a comma(,) and a number. While they agreed with the DB name in the report, they didn’t understand the number after the comma and its significance. This is how the cost analysis report looks like:

Click through for a redacted version of the report, showing an example of the database in question, as well as an explanation of what this number means.

Comments closed

Restoring a Tablespace using Barman on Windows

Semab Tariq restores a database:

I recently had the opportunity to contribute to a customer project, where the objective was to establish a system for PostgreSQL full backups and seamless restoration. Considering Barman’s successful functionality on Linux, we decided to explore its compatibility with Windows. Secondly, no other tool claims to work on Windows to take backups and perform a restore

From official documentation it is mentioned that: 
Backup of a PostgreSQL server on Windows is possible, but it is still experimental because it is not yet part of our continuous integration system.

Click through for the walkthrough.

Comments closed


Chad Callihan makes a comparison:

I haven’t had a MySQL post for awhile, so it’s time to add some variety to the blog.

There are a couple of different ways to export data with a SELECT query in MySQL: INTO OUTFILE and INTO DUMPFILE. Let’s use the MySQL Sakila sample database and walk through some examples to compare these two options.

Read on to see when you might want to use each of these.

Comments closed

Piecemeal Database Restoration

Chad Callihan restores an elephant one bite at a time…or something:

The larger a database grows, the more difficult it becomes to restore it in a timely manner. When a database is young, you might be able to manage full restores in seconds. But as it matures and backup sizes go from megabytes to gigabytes to terabytes, those restore times will expand as well.

If you plan ahead, it’s not always a requirement to restore the entire database if only part of the database is necessary. This is where the idea of piecemeal restores can save you time and wasted effort.

I’ve always found piecemeal database restoration more of an interesting idea than something quite practical. The problem is, if your data is so easily separable that you can restore one set and not need the other for some reasonable length of time, why are they in the same database? I understand that there are reasonable answers to this question, but I also rarely see those scenarios pop up.

Comments closed

Postgres Backup and Recovery with Barman

Muhammad Ali shows off another way to back up and restore data in Postgres databases:

Barman is a production grade tool for managing the backup and recovery process of PostgreSQL databases. It not only handles the physical backups but also provides automatic management of retention policies, compression, near zero Recovery point objective(RPO) and enables recovery at any desired point(PITR) in time within the recovery window.

Barman’s Stream Archiving feature stands out as a key component in achieving zero Recovery Point Objective (RPO). This is achieved by using pg_recievewal utility which continuously backs up Write-Ahead Logging (WAL) files in real-time to a designated Barman server. This capability is particularly important for applications where even minimal data loss is unacceptable.

Read on for a couple questions about it and a demonstration of how Barman works.

Comments closed

Purging Lots of Backup History

David Wiseman needs to clear out a significant amount of backup history:

Recently, I encountered an issue running sp_delete_backuphistory on servers that hosted a large number of databases with frequent log backup & restore operations. The clean up task hadn’t been scheduled and the history tables had grown very large over several months. The msdb databases was also hosted on a volume with limited IOPs.

Attempting to run sp_delete_backuphistory under these conditions you will likely encounter these issues:

Click through for that list of issues, as well as a way of mitigating the problem. I’ve noticed this kind of pattern appears fairly often in Microsoft-provided cleanup procedures: the code works well until you reach a certain scale, at which point it falls over. It’d be great if the original sp_delete_backuphistory performed batch deletion from the get-go, but David shows us a way to get around the issue.

Comments closed

Storing SQL Server Backups in Cloudflare R2

Daniel Hutmacher saves a buck:

R2 is Cloudflare’s own implementation of AWS S3 storage, with some big benefits – one of them being no egress fees, which is great if you want to publish or distribute a lot of data (like I did with this demo database). In this post, I thought I’d briefly document how to set up R2, and how to use it to back up and restore your SQL Server databases.

You’ll need a Cloudflare account to follow along. The account and a lot of their services are free, but R2 storage obviously comes with a small cost. For scale, I’m running an almost-terabyte bucket at just a couple of dollars per month.

Given the number of times I’ve pushed Daniel’s excellent Chicago parking tickets database (including right now—it’s a great database that I’ve used in several presentations and videos!), the lack of egress charges is pretty big.

Comments closed

Incremental Backup in Postgres

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