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!!

I read a post recently on The Grateful DBA that describes his analysis in testing different compression levels in LiteSpeed for SQL Server and their impact on both backup size and efficiency.  In his own words this is a “non-scientific, ground-level look at LiteSpeed’s compression ratios”, but I think this approach is a real world approach.  The post describes the pros and cons of higher vs. lower compression levels and where each is appropriate depending on the database environment.  There’s good food for thought in the blog entry and there’s  a pretty graph!  Mmm…food for thought and pretty graph….

Anyway, again, I just waned to repost the blog entry and invite discussion.  Here’s the link again:  http://troygallant.com/?p=155  -  thank you Grateful DBA.

Have a great weekend everybody and good luck getting your last minute Christmas  shopping out of the way.

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!

Today we went RTM with our latest version of LiteSpeed for SQL Server (5.2) and we’ll be going GA with this at the PASS show in Seattle, WA the week of Nov. 2.

We’re really excited about this release as it beefs up our already strong Fast Compression functionality. Fast Compression further reduces the size of backups by only backing up changed data. This improves backup times and lowers storage requirements by 70% or more over and above your current backup and compression strategy.

Again, we look forward to making it generally available during the PASS show and if you’re headed there, please stop by the booth to say hello and see a demo.  See you there!

Andy

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