Press "Enter" to skip to content

Category: Backups

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

Backing Up PostgreSQL

Published 2020-07-23 by Kevin Feasel

Valerie Parham-Thompson gives us a few methods for backing up PostgreSQL databases:

There are at least four ways to back up a Postgres database: SQL dump, filesystem snapshots, continuous archiving, and third-party tools.

For each, notes on the recovery point objective (or RPO, which is a measure of how up-to-date your application and business needs will require the data to be) and recovery time objective (or RTO, which is a measure of how quickly the restore needs to be completed after an outage begins) are provided. You should weigh these indicators against your business requirements for the data in question.

Read on to learn more about each of these processes.

Comments closed

Backups on AWS RDS

Published 2020-06-30 by Kevin Feasel

Grant Fritchey shows how you can back up a database on Amazon’s RDS:

Which results in the following:

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database ‘HamShackRadio’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Completion time: 2020-06-26T08:34:23.5511314-04:00

In short, by default, you can’t backup SQL Server databases on RDS. However, that’s by default. We can make some changes.

Read on to see the proper way of backing up a database hosted in RDS.

Comments closed

Backing Up Databases to Azure Blob Storage

Published 2020-06-24 by Kevin Feasel

David Fowler shows how you can back up databases to Azure Blob Storage:

SQL Server has given us the option to backup our databases directly to Azure BLOB storage for a while now but it’s not something that I’ve had all that much call to use until recently.

So this is just going to be a quick walk through on how you can backup your on premise SQL Servers to Azure BLOB storage. I’m going to assume that you’ve already got an Azure account, if you haven’t, you get set up a free trial which will see you good for this demo.

Performance typically won’t be as good as backing up locally to disk, so if you need the fastest backup performance and cloud storage, the best route would be to write backups to disk and have a separate process which migrates them to Blob Storage, S3, or wherever. But in many cases, doing this directly can work out just fine, especially if you are already using an Azure-based VM.

Comments closed

Database Restoration in Docker

Published 2020-06-22 by Kevin Feasel

John Morehouse gives us one way to restore a database in Docker:

Here are the steps that we will take to make this work:

1. Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
2. Make sure the container is started.  You can issue a “docker ps” command terminal to see which containers are running
3. Create a directory within the container
4. Copy the sample database backup file into the directory you just created
5. Restore the database onto the SQL instance that is running within the container

The set of steps is fine and it’s what I normally do, though someone did suggest to set up an external volume linking, e.g., /var/opt/mssql/backups outside the container. That way, you can drop your backup file in and it’ll be there without the copy step.

Comments closed