Press "Enter" to skip to content

Category: Backups

Why Restores Can Be Slow

Published 2017-05-19 by Kevin Feasel

Paul Randal explains why a database restoration tends to be slower than backing that database up:

Here’s a list of things you can do to make restoring a full backup go faster:

  • Ensure that instant file initialization is enabled on the SQL Server instance performing the restore operation, to avoid spending time zero-initializing any data files that must be created. This can save hours of downtime for very large data files.

  • If possible, restore over the existing database – don’t delete the existing files. This avoids having to create and potentially zero initialize the files completely, especially the log file. Be very careful when considering this step, as the existing database will be irretrievably destroyed once the restore starts to overwrite it.

  • Consider backup compression, which can speed up both backup and restore operations, and save disk space and storage costs.

It’s a straightforward explanation, and Paul provides a few more tips for speeding up restorations.

Comments closed

Apache Solr Backup And Recovery

Published 2017-05-17 by Kevin Feasel

Hrishikesh Gadre shows how to back up indexes in Apache Solr:

The backup mechanism allows an administrator to create a physically separate copy of index files and configuration metadata for a Solr collection. Any subsequent change to a Solr collection state (e.g. removing documents, deleting index files or changing collection configuration) has no impact on the state of this backup. As part of disaster recovery, the restore operation creates a new Solr collection and initializes it to the state represented by a Solr collection backup.

It’s probably safest to treat data in Solr as secondary data, in the sense that you should be able to rebuild the entire data set from scratch instead of Solr being a primary data store.  I’m not a big fan of the author using the term “disaster recovery” instead of just “recovery” or “backup restoration” (as they’re different concepts), but it’s worth the read.

Comments closed

Azure MySQL Backups

Published 2017-05-16 by Kevin Feasel

Grant Fritchey focuses on an area where Azure’s MySQL Platform as a Service offering really makes sense:

Why Is MySQL Platform as a Service Important?

I am going to answer this question. There are a lot of advantages to creating, using and developing against data storage within a PaaS offering. One of the biggest for me is backups. Microsoft is automatically taking backups of the MySQL databases you create within Azure. These are real, full backups. Microsoft validates the backups. As I write this, you’ll have the ability to restore your entire database, to any point in time, at intervals of five minutes, over a 35 day preceding period. By programming against a MySQL database within Azure, you are gaining protection of the information you’re storing within your database, and you don’t have to do anything to benefit from this. It’s all part of the service.

Read the whole thing.

Comments closed

Diagnosing Database Restore Wait Times

Published 2017-04-24 by Kevin Feasel

Bob Ward notes that the “100 percent processed” message doesn’t mean everything is quite finished yet in a database restoration:

Notice the “100 percent…” message has detailed about “bytes processed”. Since my data is around 13Mb this tells me that the progress indicators are all about the data transferred step of RESTORE. Notice the time gap in the messages to “Waiting for Log zeroing…” and “Log Zeroing is complete”. That gap in time is around 2 minutes. Exactly the time it took between the the 100% Complete message in the SSMS Window and the final restore message.

From this evidence I can conclude that having a transaction log file size that takes a long time to initialize can be a possible cause of seeing the behavior of getting the 100% Complete Message quickly but the overall RESTORE takes longer to complete.

There’s a lot worth reading packed into this post, as you’d expect from Bob.  Read the whole thing.

Comments closed

SQL Server Backup To Azure Tool Causing Restore Errors

Published 2017-04-13 by Kevin Feasel

Jack Li diagnoses an issue in which the Microsoft SQL Server Backup to Microsoft Azure Tool causes errors when trying to restore a database on an Azure VM with SQL Server 2008 R2:

I worked on an interesting issue today where a user couldn’t restore a backup.   Here is what this customer did:

  1. backed up a database from an on-premises server (2008 R2)
  2. copied the file to an Azure VM
  3. tried to restore the backup on the Azure VM (2008 R2 with exact same build#)

But he got the following error:

Msg 3241, Level 16, State 0, Line 4
The media family on device ‘c:\temp\test.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 4
RESTORE HEADERONLY is terminating abnormally.

We verified that he could restore the same backup on the local machine (on-premises).  Initially I thought the file must have been corrupt during transferring.   We used different method to transfer file and zipped the file.  The behavior is the same.   When we backed up a database from the same Azure VM and tried to restore, it was successful.

Click through for Jack’s findings as well as a couple workarounds.

Comments closed

Backups Causing Transaction Log Growth In Simple Mode

Published 2017-04-11 by Kevin Feasel

Andy Mallon explains why the transaction log will grow during a backup even if you’re in simple recovery mode:

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

In our librarian metaphor, she would keep an activity log, which would include the changes to books A and D from the first update, then also the changes to D, X, Y, and Z from the second update. She would not “fix” the data within the backup, but simply store those update details along with her mashed-up copy. In the unlikely event she had to recreate the books (ie, a restore), then she would go back and spend the effort to piece it back together. During that restore process, she would look at the first transaction and see that her copy of Book A in her backup was too old, but Book D already had the update, and she would roll forward the update to Book A. Next, she would process the second update and see that Books X, Y, and Z had the updates, but D still needed this second update, and she would roll forward that second update to Book D. At this point, she would have successfully reconstructed an image that is consistent to the time the backup completed.

Great metaphor to describe consistency during backups.

Comments closed

Testing SQL Server On Linux Backups

Published 2017-03-28 by Kevin Feasel

Rob Sewell confirms that Test-DbaLastBackup in the dbatools kit works for Linux:

I have written about Test-DbaLastBackup in posts here, here and here. They have been Windows only posts.

With SQL Server vNext CTP 1.4 now available and providing SQL Agent capability on Linux, I wrote here about using Ola Hallengrens scripts on Linux SQL Servers so can Test-DbaLastBackup work with Linux?

It’s a short post but good to know.

Comments closed

SQL On Linux Backups

Published 2017-03-23 by Kevin Feasel

Rob Sewell shows how to use Ola Hallengren’s solution to back up SQL Server databases on Linux using the SQL Agent:

Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell

First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs

It’s not “point, click, done,” but Rob shows you certainly can do it.

Comments closed

Compressed Backup Errors

Published 2017-03-20 by Kevin Feasel

Kenneth Fisher explains why you might get “out of space” errors when taking compressed backups:

Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.

On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.

Read on for the explanation as well as the solution.

Comments closed

TDE And Encrypted Backups

Published 2017-03-20 by Kevin Feasel

Arun Sirpal shows that Transparent Data Encryption will also lead to encrypted backups:

Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.

As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.

Comments closed