Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Bulk recovery model – Concerning about Point-in-time recovery.

Posted by Simon Cho on 08/01/2016

https://technet.microsoft.com/en-us/library/ms190692.aspx

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

 

Solution : We can run Log backup more frequently during a minimal logging operation.

https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, point-in-time recovery is not possible with bulk-logged model. Therefore, if you run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions could be exposed to data loss. To maximize data recoverability in a disaster-recovery scenario, we recommend that you switch to the bulk-logged recovery model only under the following conditions:

  • Users are currently not allowed in the database.
  • All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model.

We recommend that:

  • Before switching to the bulk-logged recovery model, you back up the log.This is important because, under the bulk-logged recovery model, if the database fails, backing up the log for bulk operations requires access to the data.
  • After performing the bulk operations, you immediately switch back to full recovery mode.
  • After switching back from the bulk-logged recovery model to the full recovery model, you back up the log again.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: