Category: Backups

Backing Up the Service Master Key

Published 2020-11-03 by Kevin Feasel

William Assaf takes us through backing up important keys in SQL Server:

You should consider complimentary backup solutions that backup/snapshot the entire server (or VM) for SQL Server, but sometimes these technologies are limited or have too much of an impact on the server. A whole VM snapshot for example that is reliant on VSS could incur an unacceptable long IO stun duration when it occurs.
Regardless, in all cases, SQL Server backups of each database should be taken regularly. This is a conversation for another blog post but a typical pattern is weekly full backups, nightly differential backups, and in the case of databases not in SIMPLE recovery model, 15 minute transaction log backups.

Read the whole thing.

Comments closed

Copying an Azure SQL Database

Published 2020-10-14 by Kevin Feasel

Garry Bargsley gives us two methods for copying an Azure SQL Database:

Copying an Azure SQL Database is a vital skill when managing cloud databases. Recently, a request was received from the “business”. They wanted to create a copy of an Azure SQL Database that was in a development environment. The database has been certified and early testing was accepted. They now want an exact copy in QA to start integration testing. The process of making an Azure SQL Database copy is straightforward. There are several different ways to perform this action.
Two methods chosen will use the Azure Portal and PowerShell to demonstrate the completion of this request.

Click through for the demos.

Comments closed

Five Methods to Take Backups

Published 2020-09-29 by Kevin Feasel

Lee Markum gives us five different methods for taking backups in SQL Server:

For those readers who like the SSMS GUI, there is good news. SQL Server Management Studio offers a fairly straightforward method for backing up a database. Once you connect to the SQL Server instance containing the database you want to back up, left click on the “+” sign next to the Databases folder. Then right click the name of the database you want to backup, choose Tasks, then choose the Backup option in the fly out menu.

If you are a DBA, it is really important to know how to take and restore backups using T-SQL or Powershell only. Especially restoring backups—in the highest-pressure situations, waiting for the UI to load can be painful.

Comments closed

The Problem with VM Backups of SQL Server

Published 2020-09-22 by Kevin Feasel

Sean Gallardy turns a problem on its head:

Now let’s get to the main point, which is how long the VM stays paused or stunned – remember, this is a “small” or “short” amount of time, one might even say “trivial”. When it is kept this short to where it’s “trivial” as in less than a second then all is good and you most likely won’t notice it except in very high workloads… but we should be running with VSS integration and not VM level so it’s still incorrect, but hey. When this time is not short of trivial then GOOD things start to happen, most notably that high availability kicks in.

I appreciate the framing of this post, as the failover wasn’t a problem; it merely exposes the actual problem.

Comments closed

The Cost of Verifying Backups to Azure

Published 2020-09-16 by Kevin Feasel

Matt Robertshaw reminds us that TANSTAAFL:

Within two weeks of backups being written to Blog Storage, we observed a significant upward trend in cost associated with a Storage Account. When compared to the previous month, there was an increase of c. £270. After some further analysis, we were able to associate this increase with “bandwidth” charges. This didn’t feel right – you don’t pay anything to upload data to Azure (ingress), you only pay when downloading data from Azure (egress).
Using Azure Monitor, we profiled the ingress and egress rates for the affected Storage Account and noticed the following pattern:
Each day, c. 150GB of backups were being written to blob storage (in blue), but shortly after, the same amount was being downloaded (in red). Over this period, we calculated 4TB of data had been uploaded and then downloaded again. Based on Microsoft’s latest Bandwidth pricing, whilst the first 5GB of egress per month is free, the next 5GB – 10TB is charged at £0.065 per month. Some simple maths confirms it to be the additional £270 we observed.

Read on for three possible solutions. My preference for an on-prem solution would be to verify locally and then push to Blob Storage / S3. Backups tend to be faster that way as well, as your disk is likely faster than your network.

Comments closed

Automating Database Restorations

Published 2020-09-10 by Kevin Feasel

Hugo Kornelis walks us through automated restoration of database backups:

Now I’ve been to quite a few conferences. And I’ve heard a lot of DBAs talk about best practices. One of the constants in those talks is: automate your restores. So I felt confident that a quick internet search would surely be enough to find me an existing script for restoring a database. Sure, I’d need to modify it to restore to a test database, but that should be minimal effort.
To my surprise, I was unable to find a script. Is scripting this so easy that every DBA can do it with their eyes closed, and nobody feels a need to share it? Is it so hard that they all decided they’re sitting on gold and will only share it for big money? Or were my internet search skills simply severely lacking?
Anyway, bottom line is that I had to do it myself. And I’ll share the result in this post.

Click through for the script and a detailed explanation of how it works.

Comments closed

Restoring a Database in an Availability Group

Published 2020-09-08 by Kevin Feasel

Rajendra Gupta walks us through the process of restoring a database which is currently in an Availability Group:

You might think a question here– We can take production database backup and restore it on the development database. What difference does it make in a standalone database restore or availability group database restore?
Database restore works with the standalone database, but if the database is configured in the availability group, we cannot directly restore the database. It requires additional steps because of the AG configurations. Our database should be in the same state (AG synchronized) after the database restores as well.
In this article, let’s cover the steps to restore an existing availability group database in the SQL Server Always On Availability Group.

Read on for the answer.

Comments closed

Restoring the Master Database

Published 2020-09-03 by Kevin Feasel

Kenneth Igiri walks us through restoring the master database in SQL Server:

The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database. It also contains most of the dynamic management views that are necessary to monitor the instance.
The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.
It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance.

It’s definitely not as easy as restoring other databases, but it is possible.

Comments closed

How a 60GB Database Backup Became 1TB

Published 2020-08-12 by Kevin Feasel

Garry Bargsley points out the importance of a tiny flag:

I put on my investigator hat and begin looking around. I started with the Windows File Server to see what was actually on the drive in question. Just as I thought, three SQL backup files were in there proper folder. Although there were only three files, something else caught my attention. One backup file was 800GB and another 1TB. That was strange as I don’t think the source databases are that big. Sure enough, I look and one database is 60GB and the other is 45GB.
Something is not right here!! So, next, I run a RESTORE HEADERONLY against one of the backup files. What did I see?

Read on to learn what Garry saw, and then what Garry didn’t see.

Comments closed

Restoring SQL Server Backups from Azure Blob Storage

Published 2020-08-06 by Kevin Feasel

Niko Neugebauer walks us through special considerations when using Azure Blob Storage as your backup location:

If you are using Azure Blob Storage for SQL Server Backups, you need to know a couple of important details before you start with some significant project and as you should know (and in my head I am keep on hearing Grant Fritchey angrily declaring that there is no backup strategy that exists, if there is no restore strategy to be found in the plan).
The ACL permissions required by the Restore From URL operation in SQL Server (any SQL Server right now, starting with SQL Server 2012 page blobs and including SQL Server 2019 blob storage support that was started with SQL Server 2014) will require … [drumroll] … exclusive WRITE-permissions on the de underlying file(s).

Niko explains some of the pain around that requirement, as well as a few other bees in your bonnet.

Comments closed