SQL Server Magazine

Features

Resources and Events

Featured White Paper

Enhance SQL Server 2005 Reporting Services

Exclusive Email Offers

========== Sponsor: Quest Software =============================

Fast, Free Database Backup and Recovery
      LiteSpeed for SQL Server Express is the high-performance, low-impact backup and recovery solution to reduce maintenance Windows and storage costs. For databases up to 10GB, LiteSpeed Express offers unbeatable compression, encryption, and even supports table-level recovery. And Quest Software is offering a full, lifetime license of LiteSpeed Express--a $295 value--for FREE.
      Get on the fast track to database backup and recovery with LiteSpeed Express. Download it now.
      http://ads.quest.com/SQLServerMagExpressNLLiteSpeedExpress091106

1. ======= Commentary ==========================================

Disaster Recovery: Express Style
      by Michael Otey, mikeo@windowsitpro.com

I suppose that the date 9/11 and disaster recovery will be linked forever. Although your first thoughts about disaster recovery might be to categorize it as an enterprise problem, disaster recovery applies to all levels of business, including applications running on SQL Server 2005 Express. At its core, disaster recovery is a simple concept: Get your SQL Server Express database and applications up and running after an unforeseen situation that interrupts application and database availability.

The specifics of a disaster-recovery plan will vary according to the needs of a business and the role of the SQL Server Express database. On one extreme, you might have a single-user database application that's read-only that doesn't require a backup plan. In this scenario, if the database or application fails because of a disaster, you simply reinstall it.

But any time you have an updateable database, you'll want to protect the time investment your users made to enter and update the data. If SQL Server Express is the core database for small-to-midsized business (SMB) line-of-business or Web-site applications, you'll want to have a robust disaster-recovery plan in place to quickly recover the database in the event of an unplanned outage. Somewhere in the middle, between a single-user and multi-user scenario, lies the single-user desktop application scenario. A business won’t stop functioning if these applications aren't available, but you still need to protect the user's work and minimize any data loss in the event of a system or site failure.

SQL Server Express offers various backup capabilities to protect your data. In "Managing SQL Server Express" (in the archive at http://www.sqlmag.com/Articles/ArticleID/93004/93004.html ) I explain how you can interactively back up SQL Server Express by using SQL Server Management Studio Express (SSMSE). And in "Backing Up Your Database" (in the same archive) I describe how to back up your database by using T-SQL scripts. You can refer to this archive for specific backup examples. However, these articles don't go into some of the more advanced backup capabilities in SQL Server Express.

In addition to a full-database backup, SQL Server Express also supports differential and transaction log backups. A differential backup saves all of the changes in the database since the last full database backup. Transaction log backups save the most recent changes to the database (called transactions) so that you can use those saved transactions to restore to a given point in time with full database integrity. Thus, changes to all related tables will be saved and restored together--keeping the data in synch. The benefits of doing these types of backups are reduced time to perform the backup and reduced media requirements. If your site needs SMB-level data protection but you haven’t implemented a backup plan, you might want to start doing a full backup at the end of each day, a differential backup every four hours, and a log backup every half hour. To recover a database using full, differential, and log backups, you first restore the last full backup. Then you restore the last differential backup followed by the log backups that have occurred since the last differential backup.

Backup is only a piece of the overall disaster-recovery picture. You need to keep two additional important points in mind as well. First, backup is useless without the ability to restore. You need to regularly test your disaster-recovery backup plan to make sure it will work when you need it. Remember that restoring a database always takes longer than backing it up, so you need to factor this extra time into your plan. Second, if site recovery is part of your plan, make sure that you have an offsite copy of your backup media. In addition, if you're backing up sensitive data, it's also a good idea to encrypt the backup. Disaster recovery is like the Boy Scout's motto: Be prepared.

*********** AVIcode *******************************************

Application Health Monitoring and Modeling
      Ensure successful application deployment, monitoring, and management when you share architectural and operational requirements. Learn how to use application models, use MOM to monitor the health of business applications, and explore a practical example of health modeling and monitoring application deployment. Download the free white paper now!
      http://www.windowsitpro.com/go/whitepapers/avicode/apphealth/?code=SQLExpMid0911

2. ==== Features =======================

From the Community: Backing Up Individual Tables

Author's Note: I received an interesting question from a reader about backups, and I thought I'd share this information with you.

Q: I'm working with a customer who's requesting an initial full backup and monthly incremental backups of specific tables in my database. I'm not aware of any way to back up individual tables. Can you provide a solution?

--Dan Collins

A: This question is interesting because neither SQL Server 2005 Express nor the other SQL Server 2005 editions provide a function to back up and restore individual tables. The main reason for this design choice is to ensure database integrity. Relational databases consist of sets of integrated tables, and transactions usually affect more than one table. Backing up and restoring individual tables has the potential to corrupt a database's integrity by letting you restore some tables but possibly not others that might have related changes. That's the reason Microsoft doesn't include this capability with SQL Server 2005. Although some third-party backup products have this capability, I recommend doing a full database backup and doing differential backups at more frequent intervals than the one requested by the customer.

Hope this helps, and as always, it's great to hear from our readers.
-- Michael Otey, mikeo@windowsitpro.com

***** HOT SPOT: Neverfail ************************

SQL Server Guide to Clustering Alternatives
      Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and failover capabilities at pricing that fits your budget.
      http://www.sqlmag.com/go//whitepaper/neverfail/sqlclustering/?code=SQLExpHot0911

******************************************************

EDITORS' NOTE: Regional Events Cover Four Key Interoperability Topics Are you a Windows fan, a UNIX diehard, or a Linux lover? Check out TechX World, an OS-agnostic event designed to give you the insider's tips on coping with your "Windows Plus" world. You'll get to spend a day with technical experts Michael Otey, Gil Kirkpatrick, Dustin Puryear, and Randy Dyess.

Designed specifically for IT professionals who work in a "Windows Plus" environment, TechX World is a four-track, one-day event featuring information about OS interoperability, data interoperability, directory and security integration, and virtualization. The content will focus on interoperability tips to help make disparate systems work well together.

The regional event series will visit four cities between October 24 and November 2, including Washington DC, Chicago, Dallas, and San Francisco. Attendees who register before August 31 will receive early bird pricing and a one-year subscription to Windows IT Pro. At $129 per person for four tracks and a full day of learning, it's worth sending the entire team to make sure you cover all the sessions. For complete agenda and speaker details, go to
      http://www.techxworld.com/registration/index.cfm?code=

Jump Start: Scheduling Backups
      by Michael Otey, mikeo@windowsitpro.com

Scheduling regular database backups with SQL Server 2005 Express is more challenging than with the other SQL Server 2005 editions because SQL Server Express lacks the SQL Server Agent job scheduling service that the other editions use. However, you can create regularly scheduled and automated backup jobs thanks to the combination of Windows' built-in Task Scheduler and SQL Server Express's command-line sqlcmd utility. First, you need a backup script similar to the following one.

BACKUP DATABASE MediaCollection
      TO MediaBackup

Next, save these backup commands in a file named myMediaBackup.sql. You can execute this file by using the sqlcmd utility. To add the backup task to Windows Task Scheduler, access the Control Panel. Click Scheduled Tasks, select the Add Scheduled Task icon to start the Scheduled Task Wizard, then click Next. Select Command Prompt from the Application list, then click Next. Enter a task name in the dialog box (use a descriptive name such as SQL Backup), select Daily, then click Next. Enter the time, select the occurrence and the start date for the backup, then click Next. In the dialog boxes, enter a user name and password for running the job, then click Next. Click the "Open advanced properties for this task when I click Finish" box, then click Finish. In the Run dialog box, enter the following command, where the –i switch identifies the file that the sqlcmd utility will run:

sqlcmd –s (local)/SQLEXPRESS –i c:\sqlbackup\myMediaBackup.sql -E

This command creates a task scheduler job that will use the sqlcmd utility to run the backup commands specified in the MyMediaBackup.sql file at the time you choose. You can read about the T-SQL BACKUP command in the article, "Backing Up Your Database" in the archive at http://www.sqlmag.com/Articles/ArticleID/93004/93004.html .

3. ==== Resources and Events ===========

ORACLE AND SQL SERVER... BETTER TOGETHER?
      Attend the 2006 Cross Platform Data roadshows to learn about optimizing 64-bit database computing, business intelligence for SQL Server and Oracle, high-availability proof points for database computing, and implications of architectural differences between Oracle and SQL Server. Coming to 12 US cities in September and October.
      http://www.windowsitpro.com/roadshows/sqloracle/?code=enews0906

SQL Server Magazine Connections Conference
      Now in its sixth year, SQL Server Magazine Connections returns November 6-9 to Mandalay Bay Resort in Las Vegas. Get down to business today with SQL Server 2005. Interact with and learn from an all-star lineup. Register for one event and attend sessions of the concurrently run events for FREE!
      http://www.devconnections.com

Is Your Antivirus Effective in Detecting Spyware?
      Are you protected company-wide against spyware, keyloggers, adware, and backdoor Trojan horses? Test a state-of-the-art scanning engine that uses threat signatures from multiple sources to track down the culprits that antivirus solutions alone can't protect you from. Download your free 30-day trial of CounterSpy Enterprise today!
      http://www.windowsitpro.com/go/download/sunbelt/counterspy/?code=0906emailannc

Simplify Management and Boost Availability for SQL Server Environments
      Learn how a database utility for SQL Server can help you lower operational costs, simplify management, and increase the availability of your SQL Server deployment. Register for this on-demand Web seminar at:
      http://www.sqlmag.com/go/seminar/polyserve/availability/?partnerref=0906emailannc

4. ==== Featured White Paper ============

Using Filters to Block Threats
      Do you want to block unwanted or undesirable email? Download this free white paper to learn how to manage the content of information crossing your network.
      http://www.windowsitpro.com/go/whitepapers/stbernard/cleanup/?code=0906featwp

===================================

WANTED: Your reviews of products you've tested and used in production. Send your experiences and ratings of products to "whatshot@windowsitpro.com" and get a Best Buy gift certificate.

5. Enhance SQL Server 2005 Reporting Services
      by Blake Eno

Dundas Software announced Dundas Chart for Reporting Services 2.0, a custom reporting solution that enhances charting and reporting within SQL Server 2005 Reporting Services. Dundas Chart for Reporting Services includes an enhanced wizard with design-time binding, design-time formulas, new 2D/3D visual effects, scale breaks, new chart types, and more. The new FastPoint Chart Type option lets you display data quickly and efficiently. For a complete list of all features and capabilities, visit Dundas Software on the Web at:
      http://www.dundas.com

6. ==== Exclusive Email Offers ====

Uncover Essential Windows Knowledge Through Excavator
      Try out the ultimate vertical search tool--Windows Excavator. Windows Excavator gives you fast, thorough third-party information while filtering out unwanted content. Visit http://www.winexcavator.com today!

Discounted Offer for the SQL Server Magazine Master CD
      Save 50 percent on the SQL Server Magazine Master CD! Order now and get access to the entire SQL Server Magazine article database on CD. Subscribe now:
      https://store.pentontech.com/index.cfm?s=9&promocode=eu2869uc

==== Contact Us ====

About the newsletter--letters@sqlmag.com
About the commentary--mikeo@teca.com
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product news--products@sqlmag.com
About your subscription--sqlupdate@sqlmag.com
About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, rresnick@sqlmag.com

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
      https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

Manage Your Account
You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
      http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

To manage your email account, simply log on to our Email Preference Center.
      http://www.sqlmag.com/email

View the SQL Server Magazine Privacy Policy.
      http://www.sqlmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.

End of Article