If you would like to participate in this month’s blog party, go to Kenneth’s invitational blog post: T-SQL Tuesday #85: Backup and Recovery.
Many of you already familiar with the NORECOVERY option for performing restores to allow you to continue to restore additional files. You have probably used this option when setting up log shipping, database mirroring, or Availability Groups. Or perhaps you need to do a point-in-time restore of a database to recover some data and had to restore multiple files. But how many of you have ever used this option with the BACKUP command?
That number is probably pretty low. It’s use case is pretty slim, but there are a couple of cases where it is very vital. First let’s take a look at what NORECOVERY does with the BACKUP command.
NORECOVERY applies only to log backups. When you run a log backup with NORECOVERY it takes the normal log backup and it also puts the database into a restoring state. This means that absolutely no transactions can run in the at database that isn’t covered by the log backup. At least not until someone recovered it. So is a scenario coming clear where that might be helpful? Think migrations.
Migrating a Database
What is one of the pain points of migrating a database from one server to another? Making sure no transactions sneak into the old database before the new database gets the last backup restored. Because this puts the database into a restoring state, no more transactions are possible. Any attempts at one will fail. It also means that any rogue clients that try to connect to the old server later won’t be able to do so. Instead of them asking you to investigate why data is missing on the new server, they are reporting that they can’t connect. An error is much better than lost data.
The process I would follow for a migration would be:
- Restore most recent full backup on new server with NORECOVERY
- Restore differential and log backups as appropriate to the new server with NORECOVERY
- Continue restoring log backups to new server (perhaps with log shipping or script of my own) until ready for cut-over
- At cut-over time, if I was using log shipping, I would drop log shipping
- Backup database log with NORECOVERY on original server putting it in a restoring state
- Restore final log backup on new server with RECOVERY bringing it online
One of the nice things about this is that if I am just migrating to a new server and not upgrading to a new version, the process is reversible. If after switching to the new server, we determine that there is an issue we can’t fix right away (performance not up to par or clients unable to connect), the database on the original server is in a restoring state ready to accept log restores. You can revert back to the old server without losing any data added to the new one by doing the same process in the opposite direction. Backup log with norecovery on new server, restore that backup with recovery on old server, and you’re back in business in just a few minutes.
This kind of leads in to the other use case. When may you want to be able to repeatedly reverse the online or recovering roles of a pair of databases repeatedly? The answer is log shipping.
Reversing Log Shipping
If you are using log shipping as a DR option, and something causes you to need to fail over to the log shipping secondary, you can use the process defined above to failover and reconfigure log shipping without having to do the full restore process. And you can repeat this process to fail back going the other way. The handling of the backups keeps both databases on the same log chain and you can simply start log shipping without having to reseed the secondary.
I won’t go into deep details here on how to do that process. I did a deep dive into that elsewhere. For more information on reversing log shipping, read my article 3 Log Shipping Techniques.
I hope you learned something today and have added Backup Log With NoRecovery to the toolbox you keep in your head. It can really save you a lot of time and headache in certain scenarios.