Press "Enter" to skip to content

Category: Backups

Backup and Restoration via pgbackrest

Published 2025-07-07 by Kevin Feasel

Warda Bibi sets up a backup management system:

Recently, we worked with a client who was manually backing up their 800GB PostgreSQL database using pg_dump, which was growing rapidly and had backups stored on the same server as the database itself. This setup had several critical issues:

  • Single point of failure: If the server failed, both the database and its backups would be lost.
  • No point-in-time recovery: Accidental data deletion couldn’t be undone.
  • Performance bottlenecks: Backups consumed local storage, impacting database performance.

Read on to see how pgbackrest can address each of these concerns.

Leave a Comment

Using Barman to Back Up HA-Enabled PostgreSQL Clusters

Published 2025-07-01 by Kevin Feasel

Semab Tariq reminds us that high availability is not disaster recovery:

Barman is a popular tool in the PostgreSQL ecosystem for managing backups, especially in High Availability (HA) environments. It’s known for being easy to set up and for offering multiple types and modes of backups. However, this flexibility can also be a bit overwhelming at first. That’s why I’m writing this blog to break down each backup option in a simple and clear way, so you can choose the one that best fits your business needs.

Click through for the available options, as well as some recommendations.

Leave a Comment

Testing Database Backups with dbatools

Published 2025-06-26 by Kevin Feasel

David Seis performs a test:

In this blog post, we will audit the dbatools command Test-DbaLastBackup. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Test-DbaLastBackup is a powerful tool to automate the validation of a backup. Backup testing should be a regular part of SQL Server maintenance and this command works well in automating this task.

Click through for a dive into the cmdlet. I’m a big fan of this cmdlet because it drastically simplifies the process of ensuring that your backups are good.

Leave a Comment

Restoring a Database via dbatools

Published 2025-06-18 by Kevin Feasel

David Seis digs into the Restore-DbaDatabase cmdlet:

In this blog post, we will audit the dbatools command Restore-DbaDatabase. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Restore-DbaDatabase is powerful tool to automate the restore of any database, and it works well in automated solutions such as daily refreshes or weekly refreshes of production to a lower environment.

David’s blog post takes a look at the cmdlet’s functionality, but also thinking about it from a security perspective.

Comments closed

Verifying SQL Server Backups via SMO

Published 2025-06-12 by Kevin Feasel

Stephen Planck does some testing:

Regularly restoring test copies of your databases is the gold-standard proof that your backups work. Between those tests, however, RESTORE VERIFYONLY offers a fast way to confirm that a backup file is readable, that its page checksums are valid, and that the media set is complete. In this post you will see how to run that command from PowerShell by invoking SQL Server Management Objects (SMO), turning a one-off verification into a repeatable step you can schedule across all your servers.

Click through for the script and explanation. I also like dbatools’ Test-DbaLastBackup command, as that can also run RESTORE VERIFYONLY but goes further and allows you to restore the backup and then run DBCC CHECKDB against its contents.

Comments closed

Testing ZSTD Backup Compression in SQL Server 2025

Published 2025-06-11 by Kevin Feasel

Aaron Bertrand runs some tests:

Whether you are a bank or a hot dog stand, creating backups is a boring but essential part of managing databases. Compressing backups – like other types of data compression – can save time and storage space, at the usually unavoidable cost of CPU. There has been little change in compression throughout SQL Server’s long history, but this year, in SQL Server 2025, there is an exciting change coming.

This set of results from Aaron is a bit different from what we’ve seen from Andy Yun and Anthony Nocentino. That’s a big part of why it’s important to get several data points, and to do your own testing in your own environment with your own equipment.

Comments closed

Performance Testing ZSTD Backup Compression

Published 2025-06-02 by Kevin Feasel

Anthony Nocentino gives SQL Server’s new backup compression format a try:

SQL Server 2025 introduces a new compression algorithm, ZSTD (Zstandard), which can help with database backup performance. The implementation of ZSTD gives you more control over your backup performance in terms of CPU consumption and backup runtime. I recently ran some rough benchmarks comparing ZSTD, and its three compression levels, with the existing MS_XPRESS algorithm, and the results are compelling and give you some additional tools for managing performance for database backups.

Click through for Anthony’s test and findings.

Comments closed

Performance Testing ZSTD Compression for SQL Server Backups

Published 2025-05-27 by Kevin Feasel

Andy Yun tries out some backup compression:

SQL Server 2025 Public Preview is not even a week old, but I’m impressed with another new capability that was released – a new backup compression algorithm: ZSTD. This one came as a surprise, despite being part of Private Preview, as it was only released with Public Preview.

Click through for Andy’s findings. It’s just one database that is not representative of normal SQL Server databases, but it’s an interesting data point that we can use.

Comments closed

T-SQL Snapshot Backups to FlashArray

Published 2025-05-21 by Kevin Feasel

Anthony Nocentino cuts out the middleman:

In this post, I’ll walk you through a T-SQL script that creates application-consistent snapshots on Pure Storage FlashArray, all from within SQL Server, no external tooling. SQL Server 2025 introduces a powerful new feature: the sp_invoke_external_rest_endpoint stored procedure. This enhancement makes calling REST APIs directly from T-SQL easier than ever. Combining this new capability with Pure Storage’s API allows us to orchestrate snapshot operations seamlessly, with no external tools or scripts required.

Click through for the process. I know that sp_invoke_external_rest_endpoint will be controversial for DBAs. That’s why I think it’s good to have examples of how it can be useful before the knee-jerk reaction of “this is automatically bad” takes over.

Comments closed