If you haven't done any restoration of your backups, you cannot count it as your retrieval strategy. It's simply a case of gambling. The backup job completes successfully once the data is written – how it will do after that is a different story. Corrupted backups, broken chains, and retrieval times that keep increasing exponentially remain camouflaged behind the success signs until you face the hard truth at 2 a.m.
It’s clear what people should do - restore their backups regularly, check them with CHECKDB, and time against RTO. However, no one enjoys doing it as the process is quite laborious. Let’s automate the whole thing.
This article will provide the readers with a complete T-SQL solution. The solution will be executed on a restore server, will identify the chain of backups on a production server, restore it, verify the outcome using CHECKDB, and log the results. Everything mentioned in the article concerns SQL Server 2016+.
The wrinkle nobody warns you about
Here’s what goes wrong with many attempts at "just restore it on another box": backup history lives in msdb on the source server. The msdb on your restore instance knows only about backups done by that instance. Point this contraption at a new restore box and ask it "what is the newest backup chain for Orders?" and it says "I don’t know, because the answer is in production and not here."
