Press "Enter" to skip to content

Category: Backups

App-Consistent and Crash-Consistent Snapshots in SQL Server

Published 2023-01-26 by Kevin Feasel

Andrew Pruski talks snapshots:

When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots.

Application consistent snapshots require freezing IO on a database allowing for a checkpoint to be performed in order to write all dirty pages to disk.

In the past, application consistent snapshots relied on the third party software to call the SQL Writer service but now with SQL Server 2022 T-SQL snapshot backups we have the ability to use T-SQL commands to freeze IO on a database in order for us to take an application consistent snapshot of the database.

Read on for more detail on the topic. With SQL Server 2022, I’ve softened a bit on using snapshots for data retention—prior to that, I was not a fan of the idea.

Comments closed

Restoring Large Power BI Premium Backups

Published 2023-01-23 by Kevin Feasel

Gilbert Quevauvilliers has a hefty backup:

When using Power BI Premium or Premium Per user you get the option to backup the database, there can be occasions when you try and restore the backup and it fails.

The reason that it could fail is because when a restore happens it can consume additional memory which would take you up and over the memory limit.

Below I will explain a new option which allow this to restore successfully!

Gilbert includes a copy of the error message and one new option in the post.

Comments closed

Backing up SQL Server with Multiple Solutions

Published 2023-01-12 by Kevin Feasel

Chad Callihan tells us a story:

Many years ago, a friend was using a backup/recovery tool for managing their backups. This particular tool on its own wasn’t necessarily bad. But in this case, it didn’t work well. In fact, it barely worked at all. Backups were slow to complete and restores were even slower. Attempting to restore even one database could take 10-15 minutes just to navigate a GUI and start the restore process.

It…was…very…slow…

Read on for the rest of the story. Most of the time, when I see two products used for backups, I typically see a bunch of redundant backups, with both products taking full backups.

Comments closed

DB Restoration and Upgrade Management for Arc-Enabled Data Services

Published 2023-01-11 by Kevin Feasel

Warwick Rudd continues a series on Azure Arc-Enabled Data Services. Part 9 covers database restoration:

Now that we have our Azure Arc-enabled SQL Managed Instance deployed, potentially one of the most common tasks you will need to undertake is being able to perform a database restore of an existing database to your newly provisioned Azure Arc-enabled SQL Managed Instance.

In our previous post, we deployed our Azure Arc-enabled SQL Managed Instance – “ae-sqlmi-demo” that we will continue to investigate and learn about how we can utilize in our data environments. In this post, we are having a look at restoring a database.

Part 10 looks at upgrade management:

Managing your Data Controller through Azure Data Studio provides an easy approach for controlling when you wish to apply upgrades supplied by Microsoft. This is something you need to stay on top of as the upgrade path is an incremental approach and only allows you to upgrade to the next version. If you leave it too long, you could be faced with multiple upgrades to be undertaken. This could lead to extended outages while you get up to date.

Comments closed

Timeouts Importing a Bacpac File

Published 2023-01-10 by Kevin Feasel

Jose Manuel Jurado Diaz hits one of the three most annoying parts of dealing with bacpac files:

Today, I worked on a service request that our customer got the following error message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding importing a bacpac file, in this situation, was at the moment that SQLPackage was enabling (rebuilding) an index, for example, Enabling index ‘IX_MyIndex’…

The other two are, of course, getting one to export without any errors and getting one to export without timing out.

Comments closed

Additional Backup Tools for MySQL

Published 2022-12-12 by Kevin Feasel

Lukas Vileikis wraps up a series:

As already mentioned, Zmanda Recovery Manager (or ZRM for short) is a member of the Zmanda family of products – Zmanda is famous for offering backup tools for MySQL and MariaDB. The company allows its customers to scale up without any issues (they offer a pay-as-you-go subscription model), and its tools are capable to back up terabytes of data in MySQL.

Read on to learn more about it, as well as a couple more tools you can use to back up a MySQL database.

Comments closed

Enterprise-Level Backups in MySQL

Published 2022-12-05 by Kevin Feasel

Lukas Vileikis continues a series on backups with MySQL:

MySQL Enterprise Backup is a known tool for everyone inside of the world of MySQL, no matter if people dealing with the tool are enterprise (business) minded or not. The tool can be considered the flagship of MySQL’s enterprise-level offerings: it comes as a part of its Enterprise tier and costs thousands to attain: is it worth your money, and perhaps more importantly, your time? We will figure that out in this blog.

Read on for Lukas’s thoughts.

Comments closed

Physical and Logical Backups in MySQL

Published 2022-12-02 by Kevin Feasel

Lukas Vileikis continues a series on MySQL backups:

Everyone who has ever backed up data using any kind of RDBMS knows a thing or two about backups. Backups are a central part of data integrity – especially nowadays, when data breaches are happening left and right. Properly tested backups are crucial to any company: once something happens to our data, they help us quickly get back on track. However, some of you may have heard about the differences between backups in database management systems – backups are also classified into a couple of forms unique to themselves. We’re talking about the physical and logical forms – these have their own advantages and downsides: let’s explore those and the differences between the two. This tutorial is geared towards MySQL, but we will also provide some advice that is not exclusive to MySQL.

Click through to learn those differences.

Comments closed

Find and Take “Missed” Backups

Published 2022-11-30 by Kevin Feasel

Tracy Boggiano checks the lost-and-found:

I blogged a few years ago about my configuration tables for Ola Hallengren’s maintenance solution that allows me to pull the configuration parameters from tables so I don’t have to edit SQL Agent jobs and allows me to query tables to see how all my jobs are configured with Ola’s maintenance solution.  I’ve updated these as I’ve implemented them at my new job.  But I was talking to another DBA about another trick I have implemented that I didn’t originally publish and it’s how I make sure I get a full or differential backup for all databases even when things fail without a bunch of manual intervention.

With Ola’s script if you default to passing in USER_DATABASES for your @Databases parameter you can query the msdb for your time period, in my case a week (168 hours), all the databases that have been backed up and return those with a minus sign concatenated together so you can exclude those from being backed up, and append those to @Databases parameter where it has USER_DATABASES already using the below code with the magic of XML and the STUFF command and pass that back to the DatabaseBackup procedure:

Read on for the code.

Comments closed