A Fine Slice Of SQL Server
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.
Leave a CommentMircea 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.
Leave a CommentTim 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.
Leave a CommentThe old adage that a DBA or SRE is only as good as their last backup is true. PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.
This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.
Click through to see how it all works. But also please test your backups, because if I were to modify the adage, I’d say that a DBA is only as good as his last tested backup.
Leave a CommentArun Sirpal doesn’t want to generate a SAS token:
I do believe most people know about the ability to backup your SQL server databases to URL (from Azure VMs). If you recall you would use the storage key ( ideally not) or a SAS token, from there you would create a SQL credential that is bound to the SAS token (as a secret). Lots of moving parts especially when it comes to rotation time. So now, if you are on the newer versions of SQL server (SQL Server 2022 Cumulative Update 17 ) start thinking about backups via managed identities.
Click through to learn how and why.
Leave a CommentJess Pomfret discovers a new flag:
Recently I was reading the docs for `Backup-DbaDatabase` and found a parameter I didn’t realise existed, but is so useful when you want to automate backups, but keep control of the file names.
Click through to learn more about the specific feature, as well as a reminder that it’s a good idea to read through the documentation. Not all documentation is good, but the work people have put into dbatools means that there is often a good example involving most of the available parameters. It turns out that there are, in fact, two examples that use -ReplaceInName in the documentation for Backup-DbaDatabase, so you get not only a description of the parameter but also two specific examples of how to use it.
Rebecca Lewis gives us a rule of thumb:
How big will my backup file be? Very good question. And of course, it depends… especially if you’re using compression.
I was working something today that required a backup beforehand for safety measures, and the server had limited storage. So, I needed to estimate the backup file size beforehand to confirm whether I had enough space for the bak file.
Click through for the script. It does, of course, include some simplifications and your actual numbers can turn out a bit different, but for a quick and dirty estimate of disk space needed, this isn’t bad at all.
Comments closedKevin Hill lays out a common but very important argument:
Many IT leaders and system admins think, “We have full backups every night. We’re covered.” But when the time comes to restore, they discover:
· The backup file is corrupt.
· The storage location is inaccessible.
· The restore process takes way longer than expected.
· The recovery model wasn’t configured properly.
· The point-in-time restore doesn’t actually bring back the data they need.At that point, it’s not a “backup strategy.” It’s a data loss incident.
The solution is to test those backups, and Kevin provides some guidance on how, as well as additional important parts of the story.
Comments closedAmar Digamber Patil makes an announcement:
While compute and data storage are already included in the Fabric capacity-based billing model, after April 1, 2025, backup storage will also be billed. However, customers will only be charged backup storage that exceeds the allocated database size.
Click through to see what’s changing and how to get ahead of this. I’m not sure there are any ways to reduce that backup price, short of managing the data in your database and not having enormous amounts of transaction log activity.
Comments closedJosephine Bush wants an extra copy of the backup:
This may or may not be helpful in the long term, but since I’m doing it to be super cautious, I figured I would blog about it. We migrated to Flex last week, and to be abundantly cautious, we’re putting the last single server backup into cold storage. You could also use this same process to offload Flex if you were going to delete a server and want to save a final backup or have some use case for saving backups to storage longer term.
Read on for the process. It’s not as simple as running a command or two, but Josephine does take us through the process.
Comments closed