Press "Enter" to skip to content

Category: Backups

Restoring a Database with Standby in SQL Server

Published 2023-08-31 by Kevin Feasel

Steve Jones stands by for station identification:

Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.

Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.

Click through to see how you can do that.

Comments closed

CPU Threads in SQL Server Backups

Published 2023-08-25 by Kevin Feasel

Andy Yun dives in:

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 closed

Restoring SSAS Cubes to SQL Server 2022 CU5

Published 2023-08-21 by Kevin Feasel

Meagan 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 closed

Backup Buffers in SQL Server

Published 2023-07-27 by Kevin Feasel

Andy 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 closed

Testing a Database Restoration

Published 2023-07-26 by Kevin Feasel

Kevin Hill fixes a problem:

Pain 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 closed

Physical Backups in Postgres via pg_basebackup

Published 2023-07-05 by Kevin Feasel

Muhammad Ali takes a backup:

The 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 closed

Clearing Backup History on SQL Server

Published 2023-06-14 by Kevin Feasel

Rich 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 closed

The Importance of backup_label Files for Postgres

Published 2023-05-26 by Kevin Feasel

Robert 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 closed