Database Recovery Models.

Database Recovery Models.


The feature “database recovery model” was included by Microsoft since SQL Server 2000. It helps us to manage the transaction log. Database Recovery model is part of the recovery options that SQL Server offers. The other option besides of recovery model is PAGE_VERIFY (CHECKSUM or TORN_PAGE_DETECTION), this option detect corrupt pages of the database because I/O problems. PAGE_VERIFY is not explain in this article.


SQL Server 2000, 2005 and 2008 offers three recovery models:

  • Simple Recovery Model
  • Bulk-logged Recovery Model
  • Full Recovery Model


The recovery model is defined by each database and it can be changed according to business necessities. The following information show us details of each recovery model.


Simple Recovery Model.

- Help to minimize the overhead over the transaction log, because transaction log is not backed up.

- Reduces the space used for transaction log on disk, because the transactions are not remaining on the transaction log, they are truncated.

- This recovery model logs minimal amount of information on the transaction log, logging only the information necessary to ensure the database consistency after a restore from a backup or after system crash.

- SQL Server truncates the log when a checkpoint occurs. Checkpoint truncates the log removing committed transactions, it is performed automatically by SQL Server at regular intervals. The intervals can be configured with Recovery Interval option using sp_configure. Is important to know that exists factors that can delay the log truncation.

- No log backup is supported. It means that if a disaster happens, a restore to point in time can not be performed and the database must be restore until the last full backup or differential backup. Therefore, the databases under this recovery model must be backed up frequently to avoid loss of data as much as possible.

- Simple recovery model is advisable to use for user databases, development and testing scenarios. Is not recommendable to use in Production systems. For production systems is recommendable Full Recovery Model.


Bulk-logged Recovery Model.

- Bulk-logged recovery model provide better protection of data than simple recovery model.

- Bulk-logged recovery model works in similar way than full recovery model. The only difference is the way to handle bulk operations. If there are not bulk operations running, bulk-logged recovery model works similar than full recovery model.

- This recovery model offers minimal logging for Bulk operations as BCP, SELECT INTO, BULK INSERT, CREATE INDEX, reducing the log space consumption.

- Microsoft recommends use bulk-logged recovery model for short periods of time. A best practice is switch the recovery model from full recovery model to bulk-logged recovery model before perform bulk operations and restore it to full recovery model when those operations are completed.

- This model is recommended for user database where bulk operations are performed when no users using the database.

- For log backup that contains bulk operations, the database can be recovered just to end of the log backup and the point in time (STOPAT) recovery is not allowed. If the log backup does not have bulk log operations logged, then the point in time recovery is allowed.


Full Recovery Model.

- This model is recommended for production databases, critical environments and in environment where you cannot afford to lose data.

- Support the recovery to point in time, assuming the log backups are complete up to the point in time desired.

- This recovery model offers fully logs for all the transaction on the database. Even logs any bulk operation.

- Transaction log, logs all the transactions of the database and the transactions are retain until a log backup is performed. Log backup is supported.


How to check the recovery model


To know the current recovery model the DBA can check it using TSQL or Graphical tool (SSMS).


Using TSQL:


SELECT DATABASEPROPERTYEX('SID', 'RECOVERY')


Or


select recovery_model, recovery_model_desc from sys.databases where database_name = 'SID'


How to switch the recovery model


The recovery model can be modified on TSQL and with Graphical tool (SSMS).


Using TSQL:


--Switch to simple

ALTER DATABASE <SID> SET RECOVERY SIMPLE

GO

--Switch to Bulk logged

ALTER DATABASE <SID> SET RECOVERY BULK_LOGGED

GO

--Switch to full

ALTER DATABASE <SID> SET RECOVERY FULL

GO


To switch the recovery model is necessary to have permissions to execute ALTER statement.


Important Considerations when switching recovery model


The recovery model should be defined depending the business necessities. But, is important to know the impact that has switching between one recover model to another.


Simple -> Full / Bulk logged

This switch is recommendable when is a productive database. For simple recovery model, the transaction log is truncated in every checkpoint, but, in full/bulk logged the transaction log is not truncate until a log backup is performed. When switch to bulk logged every transaction is fully logged except bulk transaction as SELECT INTO, but in full recovery model all the transaction are fully logged.


Bulk logged -> Full

When bulk task are finished is necessary switch to full recovery model then is recommendable take a log backup.


Full -> Bulk Logged

This switch is recommendable when a bulk task is going to be performed in order to minimize the log space used. A log backup should be performed before switch to Bulk logged and is recommendable no users connected when bulk logged recovery model is defined.


Full/Bulk logged -> Simple

This switch is not recommendable for productive databases. The log will be truncated in every checkpoint and the log backup is not supported. A log backup should be performed before switch to simple. You switch to simple before a full backup to decrease the file size of the backup.


Conclusion


The person in charge of the database should be necessary to understand the recovery models in SQL Server also the impact that has to use each of them; this article shows important information to understand that.

11 comentarios:

Park Smith dijo...

Hi VICTOR ALVAREZ,

I want to add some important point regarding SQL server recovery model.

Default recovery model of SQL server database is same like as recovery model of Model database. Model Database recovery model depends upon the edition of SQL server. If your model database is in 'Full' recovery model then your newly created database will also in 'Full' recovery model.

Thanks
Park
SQL Database Recovery Expert

Ellis White dijo...

For better suggestion visit SQL Tutorial:- http://www.sqlrecoverysoftware.net/blog/sql-database-recovery-model.html

Ellis White dijo...
Este comentario ha sido eliminado por el autor.
Kaye R. Jenkins dijo...

Use Outlook Pst Repair Tool has a unique facility to recover all the damaged data along all personal folders & attachments. It's demo version gives you facility to view preview of all recovered data and software frequently save all the recovered data into PST, EML, MSG file format.

View more: http://www.recoverydeletedfiles.com/outlook-pst-repair-tool.html

rodick willision dijo...

To repair and recover sql database, I would like to refer an effective software, you can use Kernel for SQL Database Recovery Tool. It can easy to recover all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, data types & all other components. Reference link - http://www.database.sqlrepair.org

Eric Teel dijo...
Este comentario ha sido eliminado por el autor.
Eric Teel dijo...

Here is one more SQL Server Data Recovery software which comes available with free trial version to repair corrupt or inaccessible SQL Server database.

See more :-
http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

Stanley D. Middleton dijo...

Try this utility repairs all your SQL server database tables, views, triggers, stored procedures, user defined functions, rules and indexes from the corrupted MS SQL database. You can try this tool from here: http://www.filesrecoverytool.com/sql-database-repair.html


John Walker dijo...

You can also take the help of this link to know more about SQL Recovery Model: http://sqltechtips.blogspot.com/2015/11/-recovery-models.html

john phlip dijo...

Successfully repair corrupt or damaged SQL database, use an effective SQL database recovery tool. This tool easy to recovers all tables, stored procedure, functions, views, rules, triggers and associated Primary Key, Unique keys, data types. For more detail click here - http://www.mdfrepairtool.sqlrepair.org

John Brooks dijo...

SQL Database Recovery tool to recover corrupted or damaged data from MDF file. The software also capable to restore lost data on server while running SQL Server.

See more :- http://www.tools4recovery.com/sql-database-recovery.html

Publicar un comentario