Contents
What is bulk logged recovery model?
The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk imports of large amounts of data. It’s practically the same as the full recovery model with the only exception that under the bulk-logged recovery model some operations are logged minimally.
Is point in time recovery possible in bulk logged recovery model?
If you use the Bulk-Logged recovery model, remember that you can not do a point in time restore if there are any bulk logged operations in the transaction log you are trying to restore. In order to find the exact point in time to recover you can use a log reading tool to determine exactly when the transaction occurred.
What are the types of recovery?
There are three basic types of recovery: instance recovery, crash recovery, and media recovery.
How does the bulk logged recovery model work?
The “Bulk-logged” recovery model sort of does what it implies. With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc… that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.
How does SQL Server work under bulk logged recovery?
This proves that SQL Server, under bulk-logged recovery model, logs information (page allocations) that is needed to rollback a transaction but… wait, isn’t this behavior same under FULL recovery? The answer is yes, but the difference is, under full, it logs everything and then undoes it upon rollback.
What’s the difference between full and bulk logging?
The answer is yes, but the difference is, under full, it logs everything and then undoes it upon rollback. Another important distinction to note is, under bulk-logged, it can only de-allocate the pages that gets logged as a result of minimal logged operation.
What are the bulk operations in SQL Server?
With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc… that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.