Last week, I asked y’all how frequently you ran your full backups, corruption checks, index maintenance, and stats updates. The results after over 1,000 responses:
Click through for the results and some of Brent’s thoughts.
Comments closedCategory: Backups
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 closedStephen 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.
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 closedAnthony 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 existingMS_XPRESSalgorithm, 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 closedAndy 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 closedAnthony 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_endpointstored 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.
Tim Radney violates Betteridge’s Law of Headlines:
I was recently asked if you can restore multiple differential backups in preparation for a migration. I responded that yes, technically you can restore multiple differential backups, however it will not speed up your cutover.
As soon as I read the first sentence, the answer in my head was “Yes, but why?” Tim explains the person’s reasoning and then demonstrates that this reasoning doesn’t quite work.
Comments closedMircea Dragan doesn’t want to type everything out:
I have a database backup that I need to restore on another instance of SQL Server. Is there way to generate the SQL restore command by reading data from the backup file?
Read on to see how you can generate the database restoration command for a single database file dynamically.
Comments closedTim Radney restores a database:
I have recently had the privilege of working with multiple clients who have been taking advantage of Change Data Capture “CDC”. Change Data Capture is a feature that utilizes SQL Server Agent to log inserts, updates, and deletes occurring in a table. It makes these data changes available to be consumed in a relational format. These changes are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables.
Change Data Capture is very often utilized for ETL projects or for data extracts into a data warehouse, data lake, etc.
Click through for the scenario and how you can keep CDC going after restoring a SQL Server database.
Comments closed