Press "Enter" to skip to content

Category: Backups

The Pains of Database Restoration

Published 2020-05-11 by Kevin Feasel

Stuart Moore covers some of the pains of database restoration in two posts. First, why dbatools’ Restore-DbaDatabase is a complicated as it is:

At first glance Restore-DbaDatabase looks like a slow lumberig complex beast. In reality it’s not that bad.

It’s the result of design decisions I took in wanting a solid versatile command that could cope with everything that people would want from it.

In this post, we’ll go through the main decisions/points of contention one by one

Stuart then covers the limitations of Restore-DbaDatabase:

Like all tools, Restore-DbaDatabase isn’t able to do everything that everyone wants it to. Certainly, at the moment I’d like it to write it’s own blog posts and fetch me a cold beer, but that doesn’t happen

A lot of the below isn’t complaining about people asking for features. If we can do it, we will, and we’re keen to make this work for as many people in as many situations as possible

But quite a few requests over the years have been non starters for a number of reasons.

Read them both; they’re part of Stuart’s 31 Days of Backup and Restore with dbatools series.

Comments closed

Settings for Tuning Backup Performance

Published 2020-05-06 by Kevin Feasel

Stuart Moore takes us through backup tuning options in dbatools:

Backup-DbaDatabase produces a lot of output, but the one you’re really going to be interested in is Duration. You’re job is going to be to minimise this as much as possible without impacting database performance.

To test changes in isolation you’ll want to remove the biggest variables in backup performance, network and storage speed. To do this, there’s a magical backup device that’s got near infinite speed and storage you can use. This is the black hole known as the bit bucket, /dev/null or NUL:, everything thrown into this pit disappears as fast as it’s put in.

Read the whole thing. And if you’re interested in a systematic method of testing and understanding the impact of these settings on your system, I have a presentation on the topic.

Comments closed

SQL Server Backup History

Published 2020-04-14 by Kevin Feasel

Dave Bland talks about a few useful tables in msdb:

How long a database takes to backup is something that over the years I have been asked to get.  These requests come for different reasons, sometimes it could be to find out how much it has increased over time, sometimes it could be to see if the backup job is interfering with other jobs and sometime it isn’t about duration at all, it is more about showing the backups were completed.  Over the years I have had a number of auditors ask of backup history.

In order to get this information we need to pull data from two tables in the MSDB database, backupset and backupmediafamily.

Read on to learn about these two tables and to get a sample query. On systems with a large number of databases and a DBA who loves frequent transaction log backups (like I do), this table can get pretty big, so don’t forget to prune that data over time.

Comments closed

Understanding Point-In-Time Recovery with SQL Server

Published 2020-04-02 by Kevin Feasel

Eduardo Pivaral walks us through what it takes to get point-in-time recovery of data in SQL Server:

Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the “IT guy,” you have the mission of guarantee all the data is backed up and accessible for recovery.

Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don’t question me, just backup all your databases regularly.

During my time as a DBA, I think the most frequent reason for needing point-in-time backups was “We goofed up at 2:20 PM and need to get the database back to that state,” where goof-ups typically involved mass updates or deletes of data.

Comments closed

Removing Old Backups from Azure Blob Storage

Published 2020-03-30 by Kevin Feasel

Niko Neugebauer has some advice on how to clean up backups which live in Azure Blob Storage:

Continuing the topic of the Backups to Azure Blob Storage that I have kind of kicked off with the post Striping Backups to Azure Blob Storage, I want to touch on the important aspect of “keeping it clean” – thus deleting the old backups.
On the regular Windows Server this is a rather easy task, and if you are using a standard maintenance solution, such as Ola Hallengren’s Maintenance Solution or any other one. You can also use the regular SSMS maintenance (*cough* for whatever reason that is unknown to me, that you might wish to *cough*), or you can easily set up a regular Windows Scheduler with Command Line Batch or Powershell or whatever tool/script/language you like.

The situation is quite different with the Backup To URL functionality, the one that is available since more than 6 years (and the good old SQL Server 2012 has even got a support for it in a certain Cumulative Update – SQL Server 2012 Service Pack 1 CU 2, to be more prices)

Niko goes through five different methods you can use, so check it out.

Comments closed

Striping SQL Server Database Backups

Published 2020-03-25 by Kevin Feasel

John McCormack explains why and how to stripe database backups:

First of all a definition of what we mean when we say stripe SQL Server database backups. When we talk about striping a backup, it simply means the overall backup is distributed across several files, often on different drives. Microsoft’s description.

The biggest reason for me is the time savings. I’ve done a few performance analyses and in my various circumstances, the optimal number of files to create (for minimizing full backup time) has consistently been greater than 1, even when I’m writing all of the files to the same underlying drive. The only downside I see is having to manage more files.

Comments closed

Elasticsearch Backups

Published 2020-03-17 by Kevin Feasel

Guy Shilo shows how you can back up an Elasticsearch cluster:

Elasticsearch is facing the same challenge and it’s built-in backup method is snapshots. Unlike classic storage snapshots, Elasticsearch snapshot can be stored remotely on external storage systems, and that is supposed to enable them deal with large amounts of data.

Snapshots can be stored on a shared file system (mounted on all cluster nodes), on all major cloud storage providers (Amazon S3, Azure and GCS) and on HDFS.

The documentation can be found here.

Read on to see the demo. Even if your Elasticsearch data is not the home of record and you could rebuild the cluster doesn’t mean ignoring backups is wise.

Comments closed

Backup Up Cosmos DB

Published 2020-03-06 by Kevin Feasel

Josh Smith shows us how to back up a Cosmos DB collection:

Now that ADF is a trusted service I wanted to document the state of my current solution since I’ve been able to dump the hack-y PowerShell script I put together. I haven’t been able to get the level of abstraction I’d really like to see but overall I think I’m pretty happy with the solution (and I still get to include a hack-y PowerShell script). My solution consists of

– a control pipeline,
– a notification pipeline and
– 1 pipeline for every Cosmos DB service I want to to work with. (This is because I wasn’t able to figure out a way to abstract the data source connection for the Copy Data task.)

Read on for the solution.

Comments closed