Your data is critical to your business. You need to be able to reliably back up your data─and then easily restore it if there is some sort of failure. This is still true in an environment that is running Workforce Central.

So, how much thought have you given to your backup strategy?

To some extent, your strategy will be determined by the recovery model you use. Kronos will probably recommend a recovery model, but you do have the option of choosing something else.

SQL Server provides three recovery models, each with a different approach toward the tradeoff between conserving disk space and providing granularity.

Simple Recovery Model

As the name implies, this is the simplest of the recovery models. If you choose this model, you can restore only full or differential backups─you can't restore to any specific point in time. SQL Server truncates the transaction logs when the database reaches a transaction checkpoint, and it leaves no log entries for disaster recovery. You lose any modifications to the data that may have been made between the last full or differential backup and the failure. In some scenarios, particularly those in which read-only databases are used, this potential loss can be an acceptable tradeoff for the savings in disk space.

Bulk-Logged Recovery Model

If you choose the bulk-logged database recovery model, you have the ability to take transaction log backups (and, of course, we suggest that you do so). However, you are not able to restore to a specific point in time with these log backups. You must restore the entire transaction log backup. This is better than in the simple recovery model, because at least you won't lose your data. But, you probably don't want to have your database in the bulk-logged recovery model except when you are actually performing data loads. Of course, you should have the database in the model that your ISV recommends.

Full Recovery Model

If you choose the full recovery model, you have the most flexibility on the types of backups that you can take. You can use full database backups, file backups, differential backups, and transaction log backups. The choice of the backup strategy in this scenario will vary greatly based on your database size, the backup medium, and what level of recoverability you'd like to have for the data. For more information about backup strategies in this configuration, read the following SQL Server 2008 Books Online topic on MSDN:
http://msdn.microsoft.com/en-us/library/ms190217.aspx

An important consideration for all the database recovery models is not just that you have a backup strategy, but that you also have a recovery strategy. We don't mean to be sarcastic . . . your backup strategy is only as good as your ability to use your backups. Have you tried to do a point-in-time restoration using your backups? To ensure that you are very comfortable restoring your data, you can restore your full backups to a test system and then try to apply differential or transaction logs to the recovery.

You should become familiar with both the administrative GUI in SQL Server Management Studio as well as Transact-SQL (and maybe even the Windows PowerShell environment) in performing these restorations. Learning the ins and outs of the RESTORE command in the heat of a downed server event is clearly not the way to go.

In our next post, we'll talk about how to recover other parts of your server system, outside of individual databases.