When Windows administrators and network administrators first start trying to back up SQL Server databases, they don’t understand why they can’t just use normal backup tools.  Today, I’ll talk about what’s going on behind the scenes.

Why is is So Hard to Back Up SQL Server Databases?

Windows administrators are used to doing backups by simply backing up the files.  After all, if there’s anything useful on the computer, it’s stored in a file.  By backing up the files, their work is done.

The difficulties start with open files – files that are currently locked by programs that are reading or writing to them.  When you open a Word document on a network drive, and someone else tries to open that same file, they get an error that the file’s already open by someone else.  Windows doesn’t have an elegant way of letting multiple people access the same file at the same time if they both might make changes to it.

While SQL Server is running, it keeps the data and log files open so that if anyone runs a query or updates data, SQL Server can dive into the right place to store it with a minimum of waiting time.  You could theoretically work around this by using the AUTO_CLOSE switch on databases.  This tells SQL Server to automatically close database files when nobody’s using them.  Unfortunately, this switch has some severe side effects on performance, and it’s rarely enabled.  Therefore, if we use normal file backup tools to back up a SQL Server, it will simply skip the data and log files – which are the most important ones we need to back up!

Can We Use Open File Backup Tools on SQL Server?

Most file backup tools offer an option to back up open files.  They’ll just go ahead and copy the data, keeping their fingers crossed that nobody will write to the file during that particular time.

The problem arises when backing up larger files at the same time.  SQL Server databases are usually tens of gigabytes in size or much larger, and each database has at least two files – a data file (MDF) and a log file (LDF).  They’re both dependent on each other being in sync.  If we’re trying to back them up by simply copying the files, it’s crucial that we get both files as of the exact same moment in time.

Open file backups, however, usually work on a file-by-file basis.  They’ll first back up one of the two files, trying to catch changes as they happen, and then they’ll back up the second file.  The files might be individually consistent – but when used as a pair, they won’t be synchronized.  If we’re lucky enough to capture a small database when absolutely no activity is happening, we might be able to restore it just fine.

This is where “accidental” database administrators get themselves into trouble – they test open file backups with a small database on an isolated system.  The restore works, the database attaches fine, and they decide to proceed with this approach on progressively larger production systems.  The backup software never throws errors because it thinks everything is working fine – each file is successfully backed up.

When disaster strikes and they have to restore, they restore files from the most recent backup, but when they try to attach those databases to SQL Server, SQL says they’re corrupt.  They restore older and older backups, trying to find a copy that’s in sync.  If they’re lucky, they find a copy – but even then, they lost all the data that was written to the database in the meantime.

What About SAN Snapshot Backups for SQL Server?

Open file backups are slow because it’s hard to move so much data around, but Storage Area Networks (SANs) have the ability to grab very fast snapshots.  In a matter of milliseconds, they can make virtual copies of terabytes of data all at once.  The secret is that they’re not actually making copies; they’re just tracking all changes AFTER that point to a separate place.  They make a note of any new changes that come in.

While these are faster, they’re still not a solution if they’re not perfectly managed.  For example, databases often have their data and log files on separate volumes (LUNs in SAN-speak), and the SAN may not be able to take snapshots of two different LUNs at the exact same time.  They might take a snapshot of the data drive, and then take a snapshot of the log drive, but if those two aren’t taken simultaneously, we’re right back where we started.

Furthermore, SAN snapshots have a weakness; they’re still inside the SAN.  If we’re trying to protect ourselves from SAN failure or from a datacenter failure, we still have to get that data off the SAN as fast as possible.  Another common need is to create a copy for development or QA purposes on a different server that isn’t connected to the SAN, or send data offsite for auditors to access.  In these cases, we still need to be able to get the SQL Server backed up.

This is where SQL Server native backups come in, and I’ll explain these in my next post.

Continue to Part 2

Continuing a string of posts on tips and tricks for LiteSpeed, I thought I would take an opportunity to discuss how LiteSpeed allows you to select databases to backup in a maintenance plan.  Whether or not to use maintenance plans to backup your SQL Servers is very much a matter of personal preference.  LiteSpeed has added some great functionality to make maintenance plans more configurable than what you get with SQL Server.

Consider the screen that allows you to select which databases to backup.  In SSMS 2008 (and 2005/2000) you can select to backup either all databases, all system DB’s, all user DB’s, or you can select a subset of your DB’s to backup with an associated plan.

Native Database Selector

There are two main challenges with manually selecting a subset of your databases.

1. Once you select a subset of your databases, any new database added to the system will not be automatically picked up by the maintenance plan.
2. The order in which the databases are backed up is not configurable.  The critically of your databases is not determined alphabetically, and if you have a maintenance plan run long, wouldn’t you want your most important databases backed up first?

LiteSpeed Maintenance Plans have addressed these two concerns.  In the database selector for a LiteSpeed maintenance plan, you have two options that you don’t get with native plans.

1. LiteSpeed lets you select databases for exclusion (not inclusion as in a native plan).  Essentially, you are telling LiteSpeed to backup all databases except the chosen databases.  This means that any newly created databases will be picked up by your plan, yet you can define databases that should never be backed up.
2. You can order the databases that you select.  By moving databases up and down you can determine which databases get backed up first.  I highly recommend that you let your maintenance plan tackle the most critical databases first so that should anything occur during your maintenance window, your most critical databases have a higher probability of completing successfully.

LiteSpeed Database Selector

In the example above, master and msdb are backed up first, followed by the Quest repositories, followed by the remainder of my databases.

As you can see, not only does LiteSpeed compress and/or encrypt your database backups, but also has functionality built in to assist with the management of your backups as well.  Plenty more tips and tricks to come.  If you have any questions about this or anything else, feel free to comment or visit the forums.

When an issue occurs during your backup and recovery process such as performance spikes, it is extremely beneficial to get as clear a picture as possible of what activities are occurring in your SQL Server environment.  LiteSpeed for SQL Server offers this clarity by logging these activities for immediate analysis and isolation of any bottlenecks.  This video takes you through the process of enabling logging through the LiteSpeed for SQL Server console and will cover four different areas within the product.

• Backup Wizard
• Restore Wizard
• Maintanance Plans
• Console Logging

Special thanks to April Bucher on the LiteSpeed QA team for putting this short video together. 

When it absolutely, positively has to be backed up, don’t just rely on one backup file.  Quest LiteSpeed for SQL Server can write database backups simultaneously to two places.  This protects DBAs from other users accidentally deleting backup files, from crashed file servers, and from corrupted tapes.  Learn how to mirror your LiteSpeed backups in this video by Brent Ozar:

Need to send a SQL Server backup to someone, but it’s too big and you’re not sure if they use LiteSpeed?  Quest LiteSpeed for SQL Server can back up databases into a single executable file.  You can send that file to someone else or copy it to another server, and that other server doesn’t need LiteSpeed.  The person doing the restore doesn’t even have to understand how SQL Server works – LiteSpeed takes care of all the details.

In this video, Brent Ozar shows how both the backup and restore process works:

Are you the kind of database administrator who likes to get under the hood and tweak settings for CPU affinity masking, throttling, transfer sizes, and more?  This video is for you.

Quest LiteSpeed for SQL Server has advanced configuration options that will let you finely tune how much power LiteSpeed uses, and on which processors.  Learn how in this video by Brent Ozar:

Quest LiteSpeed for SQL Server can read transaction log files (LDFs), transaction backups (TRNs) and the online transaction log – including transactions that haven’t even been backed up yet.  Learn how in this video by Brent Ozar:

Toad for SQL Server also has this transaction log reader built-in, too!

Hello! As the new PM for LiteSpeed for SQL Server, I’m extremely happy to be here on this blog. While I catch up on all things backup and recovery for SQL Server, one thing has become clear. DBA’s don’t seem to like doing differential backups. From what I gather from numerous customer calls, it seems that the current process for establishing a backup process that includes diff’s and then ensuring these diff’s work according to the predefined schedule and escalation rules is a huge pain in the a**. Well, that’s actually great to hear, believe it or not. Why? Because our advanced compression technology for managing differentials fully automates the process of performing diff’s and enforces contingencies for the differential process so that both size and dates of diff’s have an impact on when the next full will take place. We’ll be providing much more around this advanced compression as we just released 5.1.1 and are hard at work on 5.2.
Thanks! Andy Grant