Getting your SQL Backup Models right

[this was an email I just sent to the IT Team I work with]

People of the development world,

When creating databases on the SageServer (or anywhere for that matter), please consider your recovery models. Here’s a simple decision tree:

Simple Mode: you’ll manage the backups yourself, or you’ll only ever need full backups. Good for most scenarios.

Full Mode: you want to perform a weekly full backup, daily differential backups, and transactional backups throughout the day. Only use this for critical data you really care about (only on live Sage DBs at the moment).
Be extra careful when restoring one database to another. The problem comes when you leave a database in Full Mode but don’t schedule any differential/transactional backups. In this instance the database logs will grow and grow, waiting for you to request a differential review. Even full backups won’t sort the problem out. This was what happened – one of the Test databases had mushroomed to 140Gb (it’s now 600K).

Bulk-Logged Mode: We don’t use this, but a smaller but slightly riskier version of Full.

Recovery model is set in the Options tab when viewing a databases Properties.

A very good overview of all three models is provided on MSDN in the article Recovery Model Overview.

(To atone if you find out later and have a massive log (fnar fnar) – switch to simple mode, perform a full backup, then shrink files and database)

-tom