SQL Server Database Recovery Models

Microsoft SQL Server 2012 has three models that database administrators can choose from when they back up and recover SQL data. All three models are built seamlessly into Microsoft’s SQL Server application. While all three models allow users to make a backup of their SQL data, and restore data back to its condition when the backup was created, they differ in the way that they handle transactions that were applied to the database after the data was backed up.

The Simple Model

If you choose the simple model to back up your SQL data, you’ll be grabbing a copy of your entire database at a point in time and saving it. If you need to restore your database, you’ll have the entire database at the point that you grabbed the backup copy. But you will not have any of the transactions that occurred since you made your backup.databasemodel1

The simple model is ideal for people with large databases and a small number of transactions each day. If there is a paper trail for all database transactions, and the number of transactions is manageable, then the simple model will minimize administrative overhead and processing time, while preserving the backed up database files.

The Bulk-Logged Model

Like the simple model, the bulk-logged model leaves you vulnerable to data loss. You can only restore your database to the way it was when you saved it. You will not be able to automatically apply any transactions that occurred after you made your backup.

As in the simple model, detailed individual transactions appear in the transaction log. For transactions that occur as part of bulk processing, however, the bulk-logged model creates a summary-level entry into the transaction log, with no individual record logging.

The bulk-logged model is designed for organizations with large databases and a lot of bulk changes to their data. By logging abbreviated information for bulk operations, the transaction file remains small, and operations are quicker and smoother.

The Full Model

If you choose the full model, you make periodic backups of your database. Every transaction is entered into your transaction log, and the log continues to grow until you back it up and start again. If you need to restore a database, all of the information is available.

The full model is ideal for enterprises who must be able to restore their data exactly as it was originally entered. If it is crucial to have precise date/time stamps on every transaction, then you may have to use the full model and commit to the additional data administration work required to ensure that complete transaction logs are maintained. The reward is that you can restore your file to any point in time, with no data loss.

Regardless of the model that you choose for backing up your data, it is crucial to have data recovery tools available to handle those rare but devastating times when a file becomes corrupted. You need to have mdf repair software installed, as well as data administration people who are trained to use it. DataNumen SQL Recovery can identify damaged MDF files (and their associated NDF files), and repair most of them. For more information about DataNumen SQL Recovery, visit https://www.datanumen.com/sql-recovery/

Alan Chen is President and Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including Access recovery and SQL recovery software products. For more information visit www.datanumen.com

Comments are closed.