Working with SQL Server databases in Full or Bulk Logged Recovery Mode – AppAssure

One of our customers tweeted us earlier with this question: 

In AppAssure GUI it says “Truncate log after attachability check (simple recovery mode only)”, what if I'm using Full Mode?

Well, the answer took more than 140 characters. Joe Hand (Senior Director, Technical Services for AppAssure) wrote the entire soluiton up for us, and there is a new KB article coming soon as well. Thanks Doug for the question!

Issue

AppAssure 5 relies on the SQL VSS Writer to provide point in time consistent snapshots of the SQL Database. It also leverages a VSS Full snapshot once per day to perform log truncation.

Microsoft VSS only works with databases in Simple Recovery Mode. Databases that are set up in Full or Bulk Logged Recovery Modes offer other ways to backup the databases, but they do not use the VSS writers, therefore they do not truncate the logs or provide point in time consistency when combined with AppAssure 5.

When using Full and Bulk Logged Recovery Modes, the user or DBA typically uses Transact SQL Scripts to perform database and transaction log dumps as well as log truncation.

Solution

In order to leverage AppAssure 5 on a SQL Server that is running databases in Full Recovery Mode, several steps need to be taken. Place the OS and System Reserved Partition (SRP) on separate volumes from the databases. The databases would need to be on their own volume(s). The transaction logs would also need to be on their own separate volume(s). Finally, add a volume(s) to house database dumps and transaction log dumps. For example… A SQL Server with the following configuration:

In this example, you would configure this agent to back up the SRP, the OS on the C:\ drive, and the Dump Vol on the G:\ drive and omit the actual database and transaction log volumes from AppAssure backup.

Here is a Technet article on how to backup databases in SQL Server while using Full and Bulk Logged Recovery Modes. http://technet.microsoft.com/en-us/library/ms191429.aspx

This will allow the database to be recovered in the advanced modes that can recover to a specific point in time, assuming that your backups are complete up to that point in time.

Additional Information:

AppAssure 5, using Simple Recovery Mode, allows you to snapshot the database server as frequently as every 5 minutes. The huge benefit to using AppAssure 5 in this manner is that you do not need to use scripting to backup and recover anything. You simply rollback the volume(s) to a point in time of a previous snapshot and within seconds, the database can be attached and run for complete access. What you lose it the ability to roll back or roll forward the transaction logs to a specific point in time that is outside of the snapshot schedule.

Helpful Links:

Restore a SQL Server Database to a Point in Time (Full Recovery Model).

Recovery of Related Databases That Contain Marked Transaction.

The Transaction Log (SQL Server).

Full Database Backups (SQL Server)

Complete Database Restores (Full Recovery Model).