A Fine Slice Of SQL Server
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 closedMeagan Longoria runs into a problem:
I have a client who was upgrading some servers from pre-2022 versions of SQL Server to SQL Server 2022 CU7. They had some multidimensional SSAS cubes that were to go on the new server. But they ran into an issue after the upgrade. After restoring a backup of an SSAS database to the new server they found that they could no longer modify the data source using SSMS.
Read on to see what the problem was, as well as how to fix it.
Comments closedAndy Yun continues a series on backup internals:
Welcome to Part 2 of SQL Server Backup Internals. This blog series is a companion piece to my How to Accelerate Your Database Backups for MSSQLTips.com. In Part 1 of this blog series, I introduced the parts of a BACKUP operation.
Now let’s start focusing on performance characteristics. We can impact the performance of a BACKUP operation by making changes to or more of the following:
Click through for that list, as well as an extended analogy on buffer count and max transfer size.
Comments closedPain Point: Something bad happened and you need to restore a SQL Server database.
Pain Point you didn’t know you had: The backup files are all corrupt due a problem with the storage subsystem.
A backup is only as good as the last time you tested its restoration. Kevin shows just how easy it is to perform this test using DBATools.
Comments closedAndy Yun has started a new series:
Recently, I had the pleasure of delivering a new presentation called How to Accelerate Your Database Backups for MSSQLTips.com. This blog series is intended to be a companion piece, particularly for those who prefer to read content instead of watching a video.
In this first post, Andy describes the threads and queues involved with taking a backup.
Comments closedThe security and integrity of your company’s data are crucial in today’s data-driven environment. You must have a reliable backup plan in place to protect your PostgreSQL databases against unplanned calamities. In this article, we’ll examine how to create physical backups using the PostgreSQL tool pg_basebackup. We’ll talk about client needs, business use cases, backup space complexity, disaster recovery, point-in-time recovery (PITR), and how to use PostgreSQL to put these strategies into practice.
Read on for the instructions. Of particular importance is that point-in-time recovery.
Comments closedTom Collins makes SQL Server pat its head and rub its tummy:
Question: Is it possible to take a SQL Server database transaction log backup and full backup at the same time ? Are there any conflicts ?
Read on for the answer. Tom gives us a quick answer as well as some more detailed conditions.
Comments closedRich Benner reminds us to clear that backup history:
One thing we regularly come across with a new client is large system databases. Something that SQL Server should do but doesn’t is clearing up system tables. One of the more common issues is a very large backup history which causes bloat in the msdb database. Let’s find out how to clear backup history.
These tables store information about backups that you have taken, which is extremely useful … for a time. After that useful time, it’s just unnecessary data that is taking up space on your server. There’s a number of related tables that store backup history, and it’s not simple to delete data from them all. Most don’t have dates stored in the table so you have to join them together and make sure you delete in the correct order.
Read on for a built-in procedure, as well as a warning. It’s interesting to see, sometimes, just how much space msdb is taking up with stuff people don’t realize. Backup history is usually one of the offenders, along with SQL Agent history and (especially on pre-2016 instances) SSIS history.
Comments closedRobert Haas says, don’t delete that file:
I’m sure you already know what I’m going to tell you: “Of course you need that backup_label file. How could you even think that you don’t need that backup_label file?” Well, you’re right. That is what I’m going to say. But do you know why you need that backup_label file? If you were to remove that backup_label file (or fail to create in the first place, in cases where that is your responsibility), what exactly is the bad thing that would happen to you?
Read on to learn what the file does, how you get it, and why it’s so important to keep around.
Comments closedMatthew McGiffen joins two great flavors:
In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.
Read on for more information. Microsoft did the right thing: they compress first and then encrypt; otherwise, you’re not getting any benefit from the compression.
Comments closed