Press "Enter" to skip to content

Category: Backups

It’s 10 O’Clock; Do You Know Where Your Backups Are?

Published 2018-07-26 by Kevin Feasel

Adrian Buckman has a script which makes sure your backups are where msdb says they are:

Here is the information that the script provides:

  1. Warn of restores over the top of the database since its last FULL backup

  2. Show database snapshots currently against the database

  3. Show the last FULL, DIFF and LOG backup for the database including the backup durations and backup age.

  4. Backup file information such as backup start/finish time , file path , first LSN , Last LSN , a status column which states whether the log chain is in tact based on First and last LSN but also if the file exists on disk, and finally a file exists column which will tell you if the file still exists on disk.

This is a great script if you take transaction log backups frequently (typically a good idea).

Comments closed

SQL Server Backup Restoration With LOADHISTORY

Published 2018-07-20 by Kevin Feasel

Kenneth Fisher explains what the LOADHISTORY option means when you run a RESTORE VERIFYONLY command:

So, first of all, it only works with RESTORE VERIFYONLY. RESTORE VERIFYONLY does some basic checking on a backup to make sure that it can be read and understood by SQL. Please note, it does not mean that the backup can be restored. It will check things like the checksum, available diskspace (if you specify a location), the header and that the backup set is actually complete and readable. Basically enough to see if it will start restoring, but it could still have errors later on.

As for what LOADHISTORY actually does? It causes you to write an entry to the restore history table. You can tell which record this is because the restore_type is set to a V. Really, the only benefit here (as I see it) is that you can do reporting on what backups you’ve verified.

Click through for a demo.

Comments closed

Cross-Server Database Restoration With Minion Backup

Published 2018-07-02 by Kevin Feasel

Jen McCown walks us through how to restore a database on a different server with Minion Backup:

Today we’ll look at configuring a common, repeatable scenario: take the latest backup of MyDB from ProdServer1 and restore it to DevServer1. There are four basic steps to the setup and execution:

  1. Configure Minion Backup and let it run on ProdServer1.Restoring with MB requires at least one full backup taken by MB. (Note that you don’t need Minion Backup on DevServer1 for this scenario.)

  2. Configure restore settings paths. You know how sometimes a restore requires one or more “WITH MOVE” clauses?  Configure this once for your source-target pair, and MB takes care of it from then on.

  3. Configure the restore tuning settings (optional). Oh yes, we tune our backups AND our restores!

  4. Generate and run the restore statements.

It’s a good walkthrough if you’re a Minion Backup user.  If you’re not and you’re not particularly happy with your backup solution, I recommend giving it a try.

Comments closed

Backing Up Azure Data Lake Store Data

Published 2018-06-29 by Kevin Feasel

Hugo Almeida has some hints for backing up Azure Data Lake Store data using Azure Data Factory:

Our Hadoop HDP IaaS cluster on Azure uses Azure Data Lake Store (ADLS) for data repository and accesses it through an applicational user created on Azure Active Directory (AAD). Check this tutorial if you want to connect your own Hadoop to ADLS.

Our ADLS is getting bigger and we’re working on a backup strategy for it. ADLS provides locally-redundant storage (LRS), however, this does not prevent our application from corrupting data or accidentally deleting it. Since Microsoft hasn’t published a new version of ADLS with a clone feature we had to find a way to backup all the data stored in our data lake.

We’re going to show you How to do a full ADLS backup with Azure Data Factory (ADF). ADF does not preserve permissions. However, our Hadoop client can only access the AzureDataLakeStoreFilesystem (adl) through hive with a “hive” user and we can generate these permissions before the backup.

Read the whole thing if you’re thinking of using Azure Data Lake Store.

Comments closed

Backing Up SQL Server To S3

Published 2018-06-19 by Kevin Feasel

David Fowler shows how to back up SQL Server directly to an AWS S3 bucket:

I’ve been having a little play around with AWS recently and was looking at S3 (AWS’ cloud storage) when I thought to myself, I wonder if it’s possible to backup up an on premise SQL Server database directly to S3?

When we want to backup directly to Azure, we can use the ‘TO URL’ clause in our backup statement.  Although S3 buckets can also be accessed via a URL, I couldn’t find a way to backup directly to that URL.  Most of the solutions on the web have you backing up your databases locally and then a second step of the job uses Power Shell to copy those backups up to your S3 buckets.  I didn’t really want to do it that way, I want to backup directly to S3 with no middle steps.  We like to keep things as simple as possible here at SQL Undercover, the more moving parts you’ve got, the more chance for things to go wrong.

So I needed a way for SQL Server to be able to directly access my buckets.  I started to wonder if it’s possible to map a bucket as a network drive.  A little hunting around and I came across this lovely tool, TNTDrive.  TNTDrive will let us do exactly that and with the bucket mapped as a local drive, it was simply a case of running the backup to that local drive.

Quite useful if your servers are in a disk crunch.  In general, I’d probably lean toward keeping on-disk backups and creating a job to migrate those backups to S3.

Comments closed

Why Your Transaction Log Is Full: LOG_BACKUP

Published 2018-06-18 by Kevin Feasel

Jen McCown explains why you might get the error message “The transaction log for database ‘<your database>’ is full due to ‘LOG_BACKUP'”:

Your transaction log is full. Both Microsoft, and about 100 articles and blogs have covered this topic, but let’s take a quick look anyway. Because, you know, it comes up all the time.

Summary:

  1. This error message points to a lack of log backups.

  2. Make sure using sys.databases.

  3. Start backing up the log.

  4. You can shrink the log if necessary.

  5. A note on SIMPLE mode, and why it’s often a terrible idea.

This is a good summary of the problem and various solutions.

Comments closed

User-Defined Restore Points In Azure SQL DW

Published 2018-06-15 by Kevin Feasel

Kevin Ngo announces a new feature in Azure SQL Data Warehouse:

Previously, SQL DW supported only automated snapshots guaranteeing an eight-hour recovery point objective (RPO). While this snapshot policy provided high levels of protection, customers asked for more control over restore points to enable more efficient data warehouse management capabilities leading to quicker times of recovery in the event of any workload interruptions or user errors.

Now, with user-defined restore points, in addition to the automated snapshots, you can initiate snapshots before and after significant operations on your data warehouse. With more granular restore points, you ensure that each restore point is logically consistent and limit the impact and reduce recovery time of restoring the data warehouse should this be needed. User-defined restore points can also be labeled so they are easy to identify afterwards.

Creating a user-defined restore point is a one-liner in Powershell, and it’s something you could do after each warehouse load, for example.

Comments closed

Restoring Point-In-Time To Another Azure SQL Managed Instance

Published 2018-06-08 by Kevin Feasel

Jovan Popovic announces an improvement to Azure SQL Database Managed Instances:

Azure SQL Database Managed Instance enables you to create a database as a copy of another database at some point in time in the past. This is known as point-in-time restore feature, and up till now you could perform point-in-time restore only within the same instance.

The latest release of Azure SQL Database Managed Instance enables you to perform point-in-time restore of a database from one instance to another. This might be useful if you need to be sure that you could easily restore a database to another instance if there is some issue on the original instance, or if you need a database for testing or auditing purposes on the test instance and you want to use copy of some of the existing database on another server.

Click through for the current requirements and limitations, as well as a sample.

Comments closed

Issues With TDE And Backup Compression

Published 2018-05-18 by Kevin Feasel

Ned Otter describes the troubled history of the union of Transparent Data Encryption and backup compression:

The history of TDE and backup compression is that until SQL 2016, they were great features that didn’t play well together – if TDE was in play, backup compression didn’t work well, or at all.

However, with the release of SQL 2016, Microsoft aimed to have these two awesome features get along better (the blog post announcing this feature interoperability is here). Then there was this “you need to patch” post, due to edge cases that might cause your backup to not be restored. So if you haven’t patched in a while, now would be a good time to do so, because Microsoft says those issues have been resolved (although that seems to be disputed here).

My sympathies definitely lie toward backup compression over TDE if forced to choose between the two.

Comments closed

Reducing Backup Sizes

Published 2018-05-14 by Kevin Feasel

Ned Otter gives us some options for reducing the size of full backups:

SPARSE columns

IF a column contains mostly NULLs, then depending on the data type, you can achieve space savings by using the SPARSE property (documentation here). SPARSE columns can be used with filtered indexes to theoretically reduce storage space and increase query performance. But there are a boatload of gotchas, such as issues with query plan caching (filtered indexes), and the fact that if you use SPARSE columns, neither the table or indexes can have any form of compression (the documentation is clear about not supporting table compression, but does not mention index compression being an issue – but it is).

As the documentation clearly states, when converting a column from non-sparse to sparse, the following steps are taken:

  1. Adds a new column to the table in the new storage size and format
  2. For each row in the table, updates and copies the value stored in the old column to the new column
  3. Removes the old column from the table schema
  4. Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column

For large tables with even a few columns that you wanted to convert to SPARSE, this process would take forever, because you must do this for each column you want to convert.

I don’t like sparse columns at all, but I do like the rest of Ned’s options.

Comments closed