Backup Strategy for Transaction Log. Tail or Standard Log Backup?

Question

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?

Answer 1

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

Answer 2

The safe way is to:

1. Stop the applications, make certain that all the users are disconnected and make a tail_of_the_log backup of the database
2. Change the recovery model to simple
3. Upgrade
4. Change recovery model to full and do a new full backup

If this is not possible you can override the time when the database is in simple recovery mode by making differential backups

1. Take a log backup
2. Put the database into simple recovery
3. Upgrade
4. When the database is back in full recovery mode make a differential backup and then start the log backups again.

This will mean that you can restore to the point in time before the upgrade (last full backup and log backups until the last one) and if you restore the last full backup and the differential made after the database was put back into the full recovery model and any log backups made after that.

And finally the best solution: Schedule your log backups to run every 10-15 minutes during the upgrade and dont change the recovery model to simple.