Disaster Recovery: Express Style

Disaster Recovery: Express Style

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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.