Press "Enter" to skip to content

Category: Backups

Mirrored Backups

Published 2016-01-19 by Kevin Feasel

Sean McCown talks about mirrored backups:

By mirroring backups, you’re saying that you want to backup to 2 locations simultaneously.  So let’s say you have the need to backup your DBs to a local SAN drive, but also you need to send them to another data center in case something happens to your local SAN.  The way to do that in SQL is with mirrored backups and the syntax looks like this:

BACKUP DATABASE MyDB TO DISK = ‘G:\MyDB.trn’ MIRROR TO DISK = ‘\\DC1\MyDB.trn’

So above you can see that SQL will write both of these files at once, and give you a good amount of redundancy for your DB backups.  However, this can go wrong when your network isn’t stable or when the link to the other data center is slow.  So you should only mirror backups when you can pretty much guarantee that it won’t fail or lag.  And as you can guess that’s a heavy burden to put on most networks.  In the situation last week that spawned this blog, the network went down for something like 9 hrs and caused the DB’s log to not be backed up that entire time, and hence the log grew and grew.  Now you’re in danger of bringing prod down and that’s clearly not what your backup strategy should do.

Sean talks about alternatives and then talks about how they’ve gotten around the problem with Minion Backup.  If you haven’t tried Minion Backup, it is well worth your time; it’s already a great product and I use it in a production environment I support.

Comments closed

Restoring An Azure SQL Database

Published 2016-01-12 by Kevin Feasel

Grant Fritchey shows us how to restore a database hosted in Azure SQL Database:

The first, and most important thing to notice here is that it’s supplying me with a new name. I can change that to anything I want as long as it’s not the name of a database already in existence on my Azure SQL Database Server. You read that correctly, you can restore a database to Azure SQL Database, but there is no WITH REPLACE option. The restore creates a new database. This is important. In a recovery scenario, you need to be sure that you’re prepared to deal with this fact. How could you replace the existing database? Immediately run a pair of ALTER DATABASE commands to change the name of the existing database to something else and then change the name of your newly created database to the old name. That’s your choice.

There are a couple of gotchas, so if you are administering Azure SQL Database instances, be aware of these.

Comments closed

Things A Junior DBA Should Know

Published 2015-12-29 by Kevin Feasel

Kendra Little has a list of three things a junior DBA should know:

Confession: I was a Junior DBA for a long time before I had a clue about this. It’s not unusual– many DBAs pick up existing databases and it’s natural to accept that the settings are correct.

Except, usually they aren’t. Usually, the last person who set them up just kinda guessed.

Guess what? You’re responsible for whatever they guessed.

Kendra’s three items are definitely junior-level, but we all start somewhere.

Comments closed

Transaction Log Is Full

Published 2015-12-25 by Kevin Feasel

Andy Galbraith diagnoses an “ACTIVE_BACKUP_OR_RESTORE” issue:

The way FULL backups work in SQL Server, the transaction log is not released for re-use during a FULL backup, even if regular LOG backups are occurring or the database is in SIMPLE recovery.  This is due to the fact that the portion of the LOG that is used during the FULL has to be persisted during the FULL in order to be backed up at the end of the FULL – that is, the FULL backup includes the data at the start of the FULL (23:30) *plus* the LOG used until the end of the FULL (in the case of the 12/04-12/05 backup, the LOG used from 23:30 to 07:11).  This is the meaning of the ACTIVE_BACKUP_OR_RESTORE message – the LOG is waiting for the end of the active FULL backup before it can be released for re-use, which in this case was causing the LOG/LDF file to grow to fill its mount point.

This is interesting analysis and a reminder that even though you’re in Simple recovery mode, SQL Server still uses a transaction log and it’s just as important.

Comments closed

Tuning SQL Server Backups

Published 2015-12-23 by Kevin Feasel

Derik Hammer has a post on tuning SQL Server backups:

Finally, do not forget about your memory. To backup or restore a database you have to load data pages into memory. We will talk more about memory below and how the internal buffer pool comes into play and can cause operating system paging or out of memory conditions.

Derik shows the various knobs and switches available, and I want to emphasize one thing:  optimizing backup statements involves testing different scenarios.  You can make good guesses as to the appropriate MAX_TRANSFER_SIZE or BUFFERCOUNT, but even then, test different combinations and find what works best for each database.

Comments closed

Restoring CDC-Enabled Databases

Published 2015-12-23 by Kevin Feasel

Mark Broadbent shows us how to restore databases with Change Data Capture enabled:

This automatically poses the question of how it is possible to restore a backup chain with CDC? On a database restore, in order to apply differential backups and transaction logs the NORECOVERY clause is required to prevent SQL Server from performing database recovery.

If this option is required but KEEP_CDC in conjunction with it is incompatible, surely this means point in time restores are not possible for restores that require CDC tables to be retained?

-Wrong!

The answer is a bit surprising, and my guess is that most database administrators are totally unaware of this restoration quirk.

Comments closed

Get Backup History

Published 2015-12-22 by Kevin Feasel

David Alcook grabs backup history:

It’s a very common task that we have to query backup timings and other bits of info from msdb.

Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database?

MSDB has a lot of useful backup information, so if you’ve never dug into it, I recommend taking your time and seeing what is available.

Comments closed

Check Your Automated Backups

Published 2015-12-15 by Kevin Feasel

Cody Konior shows us a few cases in which automated backup tools (like Ola Hallengren’s scripts) won’t actually back up databases:

We love Ola Hallengren’s Maintenance Solution but you should always always double-check either the msdb backup history or themaster.dbo.CommandLog table to make sure any important backup was taken. This is especially important if you trigger it manually and are relying on human input to get the parameters right.

Here are three easy to miss cases where the scripts won’t backup a database. These absolutely, definitely, aren’t bugs, they’re idiosyncrasies with the underlying backup command and (sometimes) how the script works. But they’re also much easier to miss in the verbose output of the script.

The moral of the story is to check your automated backup routines and make sure that they are doing what you expect.

Comments closed

Improve Backup Performance

Published 2015-12-14 by Kevin Feasel

Brent Ozar on backup tuning:

QUESTION 1: HOW FAST CAN SQL SERVER READ DATA FROM DISK?

You can measure this by doing a full backup to disk, but use the NUL: option:

  1. BACKUP DATABASE MyDb TO DISK=‘NUL:’

This does a backup, but doesn’t actually write it anywhere, so it’s a measure of how fast the data files can be read from storage. If you run this, know that you’re taking a real backup: this backup can break your differential backups.

Vital follow-up:  Sean McCown’s talk on performance tuning for backups.  SQL Server backups have a few knobs you can turn, like buffercount, maxtransfersize, and number of files.

Comments closed