Sql-server – Backup Strategy for Transaction Log. Tail or Standard Log Backup

backupsql servertransaction-log

We are forced to perform some essential maintenance on one of our production environments. Instructions from the vendor state the recovery model of the database should be set to SIMPLE before the work is undertaken.

I'm OK with that and the business has approved the change, but I'm wrestling with the best way forward in terms of disaster recovery planning.

I want to ensure that the database can be restored up to the point of changing the recovery model, if needs be.

We take a full database backup at midnight every night, and log backups every hour between 4am and 9pm (operational hours).

This work will commence at 9am, just after a scheduled log backup, but of course I'm prepared for slippage into that hour based on various factors. I want to ensure we have a log backup in place to cover, lets say, 9.00am to 9.20am.

EDIT: Users are being locked out of the system at 9.00am or thereabouts, however I can't rely on the last log backup alone, as there may be transactions slipping past 9.00am that need to be restored. It depends when Management push the button…

What's the best course of action?

Should I just take an ad-hoc Transaction Log backup which I can, if needs be, apply after the latest Full Backup and sequence of Log Backups?

Or should I be looking at taking a Tail Log Backup, despite the fact I may never use it?

Best Answer

The real problem is that you need to know when the application is shut down, and users are out of the database. If there is a communication issue with knowing "when Management pushes the button," then perhaps you could monitor sys.dm_exec_sessions and/or sys.dm_exec_requests to determine when user traffic has subsided. Once you see that users are no longer actively in the system, you can take your final log backup, and then switch the database into SIMPLE recovery.

If you take a tail log backup (eg, BACKUP LOG...WITH NORECOVERY), you'll kick users out of the database when the DB flips into a recovering state. However, you'll still need to recover the database so that you can change it to SIMPLE and run your upgrade. If users are still in the application when you recover the database, you'll have the same possibility of lost transactions.

Once you know that all users are out of the database, a simple transaction log backup should suffice to capture all transactions.

Additionally, you can take a differential backup on the database even when the database is in SIMPLE recovery mode. Differential backups don't give the same point-in-time recovery as a log backup, but if you are truly looking for a recovery point of "immediately before I start my maintenance," then using a differential might be your best option. This option has two main benefits:

  1. It achieves the goal of having an easy restore point that includes all changes to the database up to a point just prior to the maintenance.
  2. Recovering to that restore point requires restoring only the full + differential backups. This will likely reduce the time & effort necessary to restore, compared to full + N logs.

Compared to taking one final log backup, the differential will take longer to run, and will require more disk space on your backup share. The exact time & space will vary depending on how much data has changed since the last full backup, so that is something you will need to evaluate on your own.

Additional information on differential backups from Microsoft: https://technet.microsoft.com/en-us/library/ms191180(v=sql.105).aspx

Related Question