LOG Backup with SQL Server? September 14th, 2010

Vinod Kumar
Backing up the transaction log is a very important part of a disaster recovery plan to be able to restore the database to the closest point to a point of failure.image This is critical to the business and most importantly part of any applications deployment plan. The term log backup chain is often used to describe the sequence of log backups that can be restored in sequence after restoring a SQL Server database backup. A simple database cannot have its logged backup and therefore cannot have a log backup chain. A log backup chain is broken when some operation invalidates the ability to backup the log and restore it after the previous. Examples of operations that breakup the log backup chain are changing the recovery model of a database from FULL to SIMPLE or executing the BACKUP LOG WITH TRUNCATE_ONLY or NO_LOG command.
It is also important to remember that the size of a transaction log backup may be larger than expected if the database recovery model is bulk-logged. This is because the transaction log backup will contain a record of all bulk operations since the last full or differential database backup.
BACKUP LOG WITH NORECOVERY
This option is used to backup the current tail of the log and leave the database is a state where transaction logs from a standby server could be restored to bring the database back into a consistent state.
Consider this scenario. You have a primary database but are deploying a standby server where you copy over and constantly restore logs. Now for some reason the primary server has a problem and you want to failover (not cluster failover) and use the standby server as the new primary database server. If the primary server database is still available, simply use BACKUP LOG WITH NO_RECOVERY to backup the current tail of the log and apply to the standby server to bring it to a consistent state and the appear to be the same database as on the primary.
Now you start using the standby server and start backup up logs on that database. Now you want to switch back to the primary. Since you used WITH NO_RECOVERY you don’t need to restore a full database backup from the standby server. Simply take any log backups produced on the standby and restore them on the primary.
There are so many interesting hidden behaviors as a DBA you might want to explore and understand. Hope this blog is demystifying some of these.

Tags: , , , , , , ,

This entry was posted on Tuesday, September 14th, 2010 at 14:24 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


2 Responses to “LOG Backup with SQL Server?”

  1. I usually don’t post in Blogs but your blog forced me to, amazing work.. beautiful …

  2. titania says:

    very informative. thanks for sharing this.

Leave a Reply