Backing Up SQL Server

WebBrix stores its data in SQL Server databases. These databases should be backed up on a regular basis so that data can be recovered in the event of a disaster--for instance a hard drive crash.

1.	Database (or Full) Full database backups are the default and serve as a start point for all other types of backups. A full database backup does what it sounds like--it backs up the entire database. A full backup can be large, as it contains all data in the database.

2.	Differential Differential backups include only the data in the database that has changed since the last full backup. A differential backup will be a much smaller than a full backup (that takes much less time to create) because it includes only the changed data.

Transaction Log Backups of the transaction log provide a history of the transactions that have taken place within the database. The backups of the transaction log are then used to restore the database fully to a given point in time. Transaction log backups are smaller and are taken more often than the other types of backups.

Each SQL Server database has a recovery model. The recovery model determines whether and how transaction logs can be backed up. The recovery model chosen has a significant impact on system performance (especially during heavy data loads) and on data recovery options. Recovery Models are viewable and changeable in SQL Server Enterprise Manager or SQL Server Management Studio. SQL Server supports three recovery models: Simple, Full and Bulk-Logged.

When using this model, SQL Server maintains a minimal amount of information in the transaction log. SQL Server truncates the transaction log each time the database reaches a transaction checkpoint, leaving no log entries for restoration.

When using the simple recovery model, it is only possible to restore full or differential backups. It is not possible to restore the database to a given point in time since there are no transaction log backups to restore (transaction log backups are not supported under the simple recovery model). As a result, the database can only be restored to the exact time when a full or differential backup occurred. All data changes between the time of the most recent full/differential backup and the time of the failure are permanently lost.

When using this model, SQL Server preserves the transaction log until you back it up via transaction log backups. The consequence of this is that the transaction log MUST be backed up. If not, it will grow until it has consumed all available disk space.

Using the full recovery model provides the most flexibility when restoring databases. In the case of a disk crash, the database can be restored more fully then under the simple model, as transaction log backups can be applied after the last full/differential backup bringing the recovered database closer to the state it was in at the point of the crash. (If the disks are still readable, SQL Server may even be able to recover all the way up to the crash moment.) In the event of data corruption, the full recovery model allows the database to be restored to a specific point in time. For example, if some data change corrupted the database at 4:27AM on Monday, SQL Server’s point-in-time restore can be used to to roll the database back to 4:26AM, wiping out the effects of the error.

The bulk-logged recovery model is a special-purpose model normally only used for short periods of time. See SQL Server documentation for more details.

Each organization must decide what SQL Server backups/models best meet its needs. However here are some general possibilities:

Full Backups once a week, Differential Backups on the other days, Transaction Log Backups every 2 hours

SQL Server Management Studio can be used can be performed one-time backup/restore. It can also be used to create Maintenance Plans to automatically performed the required backups on a regular basis.