There are features of SQL Server I use often and consider to understand reasonably well, features I know but rarely use, and probably a whole list of things I’ve only looked at or used once. Within the realm of things I know, sometimes I find that I had an incomplete understanding or missed a dark corner because I never needed to explore it. While chagrin or the Picard facepalm are natural reactions to “I thought I knew that”, better to appreciate that I now have one less gap in my game than before. In this case, as you’ve probably surmised, it relates to restoring with standby. I’ll start with a quick overview of log backups, norecovery, and standby, then show you how it works with some examples including one that illustrates the lesson I learned and wrap it up with some notes on why this bit of restore trivia might save you some aggravation one day.

Log Backups, Norecovery, and Standby

We run log backups to protect data since the last full backup and to give us the ability to restore to a point in time if needed, right down to the second. The magic that underpins that is using norecovery during the restore, allowing us to continue to apply log backups until we’re finally ready to finish the restore effort and start using the database.  That works as long as we have a complete log chain and we’re restoring to the same version or later of SQL Server (with some limits – for really old versions you might have to restore to an intermediate version, backup, then restore to the newer version). You may also have used or at least thought of this as the basis of log shipping, which leverages the capability to maintain a disaster recovery server. Standby is a variation of this that allows us to read from the database during the time when the final restore has not been completed.

Why would we need to look at the objects when we haven’t finished the restore process yet?

• We want the comfort of confirming that we really are applying log restores as expected
• We want to use that database for reporting, knowing that every time we restore an additional log backup, we’ll have to disconnect all other sessions to that database. A tradeoff to get more value out of the process.
• We need to view the tables to see if we have restored far enough, a typical data research or disaster recovery scenario such as “I think I dropped the table at time X”

Restore with Norecovery - SQL 2019 to SQL 2019

Here’s a simple example of restoring a full backup and a log file with norecovery:

RESTORE DATABASE [Test] FROM DISK = N'C:\Andy\Test.bak' with MOVE N'Test' TO N'C:\Andy\Test.mdf',  MOVE N'Test_log' TO N'C:\Andy\Test_log.ldf',  NORECOVERY
RESTORE LOG [Test] FROM DISK = N'C:\Andy\Test_log.bak' WITH  NORECOVERY

Browsing in SSMS after running those two statements we can see that the database shows “Restoring…”, which means we used norecovery on the restore and that we can’t look inside the database. We know it’s there, that it seems to be right, but we can’t look at any objects in the database. We can continue restoring more changes or we can bring it online (and then can no longer apply log changes).

Restore with Standby - SQL 2019 to SQL 2019

If we change to restoring with standby we will be able to query the database. It's a minor tweak to our syntax that works almost the same as the basic norecovery restore but adds a standby file on disk. That file is the magic that handles copy-on-write operations so that we can continue applying log backups. Here’s an example that uses standby instead of norecovery:

RESTORE DATABASE [TestCopy] FROM  DISK = N'C:\Andy\Test.bak' with MOVE N'Test' TO N'C:\Andy\Test.mdf',  MOVE N'Test_log' TO N'C:\Andy\Test_log.ldf',  standby = 'c:\andy\testcopy_standby.dat'
RESTORE LOG [TestCopy] FROM  DISK = N'C:\Andy\Test_log.bak' WITH  standby = 'C:\andy\testcopy_standby.dat'

Now we can view objects in our TestCopy database and even run read only queries. Note how it’s labeled “Standby / Read-Only” where earlier it was “Restoring…” so you can easily tell what’s going on.

Restore with Norecovery - SQL 2019 to SQL 2022

Those examples used a SQL 2019 backup and restored to the same instance, just to make for a simple demo. What happens if I want to take those same backups and restore to SQL 2022? Here is the same norecovery restore:

RESTORE DATABASE [TestCopy] FROM  DISK = N'C:\Andy\Test.bak' with MOVE N'Test' TO 'C:\Andy\Test.mdf',  MOVE N'Test_log' TO N'C:\Andy\Test_log.ldf',  norecovery
RESTORE LOG [TestCopy] FROM DISK = N'C:\Andy\Test_log.bak' WITH norecovery

As expected, this works fine.

Restore with Standby - SQL 2019 to SQL 2022

Now let’s try restoring the SQL 2019 backups to SQL 2022 using standby:

RESTORE DATABASE [TestCopy] FROM  DISK = N'C:\Andy\Test.bak' with MOVE N'Test' TO N'C:\Andy\Test.mdf',  MOVE N'Test_log' TO N'C:\Andy\Test_log.ldf',  standby = 'c:\andy\testcopy_standby.dat'

It fails! That’s the piece I had missed all of these years.

With Standby Fails If Versions Don't Match (and a few other caveats)

Here’s what it says in the docs:

A database upgrade would be necessary in this case because it’s a totally different version. We can see that if we restore with norecovery and then finish the recovery, we get a bunch of messages about the internal upgrades being applied:

Lesson Learned

If you’re restoring with standby just to do some research, knowing this limitation means picking a server that has the same version before you do the restore. If you’re relying on standby to allow read access to data except during restores it means that you have to coordinate any future upgrades of the source or target server. If you can upgrade both at the same time, not a big deal. If you can’t do them both at the same time, you’ll have to move either the source database or target database to a different, version compatible server. Doable either way, but less aggravating if you know you have to do the work rather than finding out after the source server has been upgraded.

That’s my small lesson learned and I hope it helps someone learn something as well. For those reading who haven’t tried norecovery, standby, or log shipping, I encourage you to spend an hour giving it a try.