Full Recovery without Log Backups

14 September,2014 by Tom Collins

When a database is in Simple Recovery Mode , log space is automatically reclaimed when a transaction finishes.  This means log space management is not required .

In the event of database loss , only data from the last back is recoverable. Any data since the last backup is lost, and needs to be recreated.

If the recovery Point required is different, change the recovery mode to FULL and take log backups. Regular log backups will ensure a data file loss  does not mean a catastrophic failure, but only data loss up to the last log backup. Recovering a database to a specific point in time is  critical to some applications , such as financial data systems.

I review databases as part of my daily routine, I check for databases in Full Recovery Mode  with no log backups. I speak to the application owner to establish the recovery point.  If the recovery point is last full backup , there is an option to use Simple Recovery . If the recovery point after the last full backup , a log backup schedule is set up to support the recovery point

Establishing a recovery point is part of creating a Disaster Recovery Plan

Read More

Factors that can delay Log Truncation – LOG BACKUP

SQL Server - How to find Open Transactions

SQL Server SLEEPING MODE , locks and transactions

MS DTC and sys.dm_tran_active_transactions

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Full Recovery without Log Backups

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer