How is SQL DB handled at VM restore

[mrholm]

Hi
A question came up around large shared SQL hotels and using Veeam. The hotel is a VM with several SQL instances and is backuped using Veeam with Guest Interaction at 8PM every night. In conjunction to this the t-logs are backuped every hour.
If the VM crash today at 2PM due to any trouble and we restore the VM back to last known state it will be back to 8PM last night and this means that the full VM is restored and SQL databases as well. I guess that Veeam is not putting the databases in restoring mode so we will not be able to use the t-log backup? How is this to be handled at a VM restore to get the t-logs back as well otherwise the full restore of a VM is worthless for databases in Full Recovery Mode.
//Mats

[soncscy]

Hey Mats,

I think there are two approaches you could take:

1. Restore the Entire VM, and then restore the DB to the desired state right away afterwards
2. Deploy a fresh VM from Template (potentially faster) with SQL pre-configured, and then just use Veeam to restore DBs right to the state you want.
(Bonus 3rd) - Fire up an instant recovered instance to handle the day-to-day transactions and avoid downtime and I suppose you should be able to replay the logs against the Instant Recovered VM as well, but this one I'm not sure the benefit is so great.

https://helpcenter.veeam.com/docs/backu ... ml?ver=100

This step lets you set the recovery state, and the same tooling lets you recover the DB to point in time as long as you're doing transaction log backups with Veeam.

So I think you just need to do some internal testing and see what works best for your environment/team -- what are they most comfortable with as the DR strategy? I've seen clients opt for it both ways.

[mrholm]

Thanks for the answer
The first is not acceptable since we’re talking about roughly 4-8 TB of DB data to restore in the hotel so if I have to restore twice (VM and then all DB) this will take too long time.
The second seems like a way to go even if it will take time to do the DB restore but since we have developed a Powershell script to restore 8 DB’s in parallel this is the preferred choice I guess.
The third option I don’t really understand, if I do an instant recovery of a VM that’s just the same as do a restore and I still need to start by restoring the databases onto it to be able to replay the logs.
I’ll share this with my DBAs to see, thanks again

[soncscy]

Hey Mat,

Sure, you're welcome.

But for instant recovery, correction -- the Instant part of Instant Recovery isn't a joke -- it really is a fully running VM in a minute or two. Basically if you don't already have a standby, this gets you a standby "instantly", and you can just restore into it. Now, the machine is running on snapshots effectively, so this is more of an emergency situation, but either way you're going to have to make a concession somewhere.

Instant recovery gets you a SQL server "now". It comes with a cost, but you're live in as fast as the session takes to start.