In my last post, I talked about why we can’t use open file backup tools with SQL Server, and today I’m going to discuss SQL Server’s built-in solution: native database backups.  The word “native” means it’s an option that ships inside the SQL Server box.

SQL Server’s BACKUP DATABASE Command

SQL Server has a BACKUP DATABASE command that…

Wait, don’t close that browser!

I know – you don’t want to learn a command, and don’t worry, this isn’t going to be a session on how to use BACKUP DATABASE.  I’m not going to teach you any of that.  In fact, I’m pretty much going to teach you how to avoid writing any commands whatsoever.  But in order for me to explain the pros and cons of SQL Server backup solutions, I’m going to have to teach you the bare minimum of what BACKUP DATABASE is doing behind the scenes.  Now back to the story….

The BACKUP DATABASE command runs for just one database, and it produces a single flat file that contains enough data to reconstruct a perfectly synchronized data and log file for that database.  It can put that file on local disk, on a network file share, or on a tape drive.

How SQL Server Backs Up Open MDF and LDF Files

SQL Server doesn’t need to pause activity while it makes a backup.  As SQL Server makes a copy of the data file, it uses the log file to track what’s happening inside the database.  Every transaction is logged as it occurs, so SQL Server has a running list of changes by time.  When the data backup is complete, SQL Server also copies the log file data into the backup file too.

When it’s time to do a restore, SQL Server:

• Opens the backup file
• Reads out the data contents, and writes those to the data file (MDF)
• Reads out the logged changes that happened during the original backup, and writes those to the log file (LDF)
• At this point, the restore process is basically done.  We’ve got complete copies of the data and log file – but that doesn’t mean the data is consistent, so the next step kicks in….
• The recovery process reconciles the data and log files to put the database at a point in time where no transactions have left records halfway modified.

The recovery process only results in a perfect copy of the database when it has log file records that match up to how the database looks.  It can fix some things by rolling transactions forwards or backwards, but it can’t fix data records that don’t match up to anything resembling what’s in the log.  This is the problem with the open file backup scenario – if SQL Server isn’t keeping track of transactions in the log file (LDF) when the data file is copied, or if the transactions have been cleared out of the log file before we take a copy, the recovery process may not work.

Sounds great, right?  What’s the catch?

The Drawbacks of BACKUP DATABASE

Unfortunately, the BACKUP DATABASE command does just that – it backs up a single database.

Sysadmins want to back up the entire server, or all of the databases on a server.  There’s a few ways to schedule this command to happen regularly for all of the databases on a server, but none of them are really point-and-click easy for Windows administrators who’ve never opened up SQL Server Management Studio before.  Even if you manage to do it successfully, you’re going to have a tough time restoring some things.

Security data about logins is stored in the master database.  While this is technically a database and BACKUP DATABASE does back it up, most people don’t restore this database.  When disaster strikes, they build a new server, install SQL Server on it, and SQL Server lays down its own new master database.  Restoring the master database isn’t impossible, but it’s just difficult enough that it freaks people out when they’re under duress.  They decide to just start restoring other user databases instead, and deal with logins later.

Similarly, information about scheduled jobs, linked servers, and server-wide tweaks like configuration settings are not easy to restore.  They’re all possible – but they’re not intuitive for sysadmins facing an outage.  They’re also uncommon enough that full-time DBAs aren’t even familiar enough with these processes to do them by heart.

The Easier Way Out

As a result, many Windows administrators take the easier way out – they buy an add-on product for their file backup software so that it’ll also handle SQL Server.  Backup products like Acronis, Backup Exec, NetBackup, Tivoli, and the like offer options to back up SQL Server.

Some of these products simply call SQL Server’s native BACKUP DATABASE command behind the scenes.  They intercept the backup output and direct it to the backup software rather than a flat file.  These products claim to back up all of the databases, but at restore time, they feature the same challenges I discussed above.

Other products take a snapshot backup of the entire server by integrating with Microsoft’s Virtual Shadow Copy Service (VSS).  Some of Microsoft’s enterprise products (including SQL Server and Exchange) have VSS support so that the snapshots will be consistent enough for the recovery process to work.  These products can restore the entire server quickly – but they have other drawbacks, like not having an easy time integrating with log shipping or development servers.

Is Easier Better?

In my next post, I’ll explain why easier isn’t necessarily better, and why you might want to pay a little more attention to backing up specialized applications like SQL Server and Exchange.

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

More, different, or better?  Whatever you need, our SQL Server community at http://sqlserver.quest.com is there to help.  Many of you already frequent our forum location to engage in discussions about our database management products, but to improve upon the experience we’ve recently added a sticky topic specifically for you to request videos and collateral.  Do you want to know how Spotlight on SQL Server Enterprise can help you identify the I/O latency for an instance using SAN storage, and feel that a short video would be more helpful?  Would you like a brief tutorial on how to make the most out of the History view in Foglight Performance Analysis for SQL Server?  If so, we want to hear from you here: http://sqlserver.quest.com/forum.jspa?forumID=888, which is the direct link to our forum area entitled “Request Product Videos and Collateral Here”.  We’re looking forward to hearing from you!

Looking to use LiteSpeed for SQL Server for Log Shipping?  Check out this video, created by Nina Philippova our lead technical writer for LiteSpeed, for a step-by-step overview of configuring a Log Ship plan through the LiteSpeed UI.  Please provide any feedback that you may have, we’d love to hear it.  Thanks!

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.

One question that comes up quite a bit pertains to how you can handle errors generated by the LiteSpeed Extended Stored Procedures (XP’s) in your own custom scripts.  Scripting with XP’s is fairly straight forward, because they accept parameters just like any other stored proc, but how they handle errors is a bit different.  Unlike  standard SQL Statements that will populate @@error and can be handled with TRY/CATCH blocks, XP’s simply return an error code.  By capturing the value returned by an XP you can succesfully trap and code around many types of errors.  View the following block of code to show how this is done.  In this example, the drive I am trying to backup to does not exist, therefore the backup fails with error code 50003.

DECLARE @rc INT
EXEC @rc = master.dbo.xp_backup_database
  @DATABASE='master'
  ,@filename ='V:\backup\database.BKP';
SELECT @rc

I know some folks out there have done some pretty cool things scripting with LiteSpeed.  If anyone has any scripts they’d like to share, we’d love to take a look!!

Microsoft SharePoint content databases are notoriously difficult to compress.  Users upload Word documents, Excel spreadsheets, and PowerPoint presentations into the SharePoint sites, and these bulky files go straight into the SQL Server database.  They’re tough to compress, and the files just keep piling up as more users start using the company intranet.

Even worse, us database administrators are told that SharePoint is now mission-critical, and we have to make sure we back it up as fast and as often as possible.  How do we do it?  In this five-minute video, Brent Ozar shows how to use LiteSpeed’s new FastCompression to pull it off.

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. 

Say 'hello' to my little friend!

We’re very excited to introduce a new addition to LiteSpeed brand – we’ve released LiteSpeed Engine for SQL Server, which is now available with LiteSpeed for SQL Server Enterprise.  Let’s nail some questions that you may be having:

• What is LiteSpeed Engine for SQL Server?  It’s a very light weight, drop and go compression and encryption ‘engine’ for SQL Server.

• Why is that important?  By offering LiteSpeed Engine for SQL Server, customers who already have a backup and recovery strategy in place using their own scripts and processes can now get the compression and encryption that is offered by LiteSpeed – without changing their current backup processes.

• What’s the difference between LiteSpeed Engine and LiteSpeed Enterprise?  LiteSpeed for SQL Server Enterprise offers the full monty – a very comprehensive backup and recovery solution that provides not just compression and encryption, but also Maintenance Plan management, Object level recovery, Fast Compression, etc.  Now, LiteSpeed Engine for SQL Server, as stated in the previous bullet, simply offers the compression and encryption capabilities of LiteSpeed which fits into an already established backup and recovery process.

• Can you give a brief usecase for this?  LiteSpeed Engine provides much more flexibility and choice to you as to how to deploy LiteSpeed.  For example, on your ‘mission/business critical’ systems, the full implementation of a backup and recovery strategy offered by LiteSpeed Enterprise will be very valuable.  On those less critical systems, or pre-production environments, or those databases that already have a backup and recovery plan in place, LiteSpeed Engine will be the missing piece that gives compression and encryption.

• How do I get LiteSpeed Engine for SQL Server?  Currently, LiteSpeed Engine is available to evaluate by downloading from our LiteSpeed for SQL Server page on Quest.com.  Take a look – we’d love your feedback.

• Is there any other resource where we can see how LiteSpeed Engine for SQL Server will work?  Yes!  We have a brief video that provides an overview of LiteSpeed Engine for SQL Server and how to configure.

We’re really excited about our introduction of LiteSpeed Engine for SQL Server and are confident that the flexibility now available in how you decide LiteSpeed fits into your backup and recovery strategy will blow you away.

Quest’s new LiteSpeed Engine for SQL Server is a transparent way to compress and encrypt your Microsoft SQL Server backups – without changing your existing backup scripts or maintenance plans.  Instead of calling special stored procedures at backup time, just configure the Engine once and be done with it.  Learn how to set it and forget it in this video by Brent Ozar.