how 2 restore sql server (vm) and rollforward db to the latest state

Hi everybody,

I just asked my self the question: ‘what will I do when I need to restore my SQL-server-VM and do need to rollforward to the latest state of the database’.

Assume you have a vm with a SQL-server. You’re backing up this vm every 5 hours and you have configured a transaction log backup every minute. Now something bad happens inside your vm and you know you have to restore the vm from a restore point the day before - say you cannot access the product vm anymore to keep it simple. Whatever you do (IR, CDP replica, normal replica), your vm runs but the database contains old data, you need to rollforward to the latest backed up transactions.

If you just restore you vm with the networks connected, some applications might access the database and would probably insert data - that would be a huge mess. So you’d disconnect the networks, probably login to your vm to delete the database, then connect your networks. Then you could run the instant database recovery added in v11 (I love this feature) or a direct restore - whatever you like, but it is kind of a manual process and it isn’t that quick as you’d like or probably expect (if you just think of a normal IR in comparison).

So I’m wondering if anybody else has ever done anything like this before, probably with a better approach and I wonder if veeam is probably working on a better solution like IR + IDR at once.

Thanks!

Be the first to comment!

Willing to learn if there’s a better way but I’ve always done Instant VM Recovery and then just triggered a restore database to include the transaction log roll-up.

This is the procedure I have done up to now, too. And I think that is what is intended by Veeam.

Willing to learn if there’s a better way but I’ve always done Instant VM Recovery and then just triggered a restore database to include the transaction log roll-up.

This is the procedure I have done up to now, too. And I think that is what is intended by Veeam.

I agree with this as well for SQL restore. Done this as well.

Yeah but if somebody uses the database between the IR and the database restore, you have (unknown) data loss...

True, if possible I’d take down the application for the recovery process and return access once the database is ready for use.

Yes, but only possible if you have control over the network and the application. In our case, the application is hosted somewhere else and so I’d have to disconnect the network… That’s why I thought there should be a better way...

As a rule of thumb, when doing VM level restores, I always opt for a restore to an isolated network or without a network connection.
That way it can be checked first before bringing back into production.

okay, thanks for your comments! Does anybody know if the PM’s take a look at this conversations regarding the feature requests? Or would I have to post that on the forums? Thanks!

There’s some visibility on here but the R&D forums is gonna be the best place to raise it

@Rick Vanover: We talked about this case a while ago and you wannted to have a look at it. Do you have any news for me meanwhile? Thanks!

Hi @mcz I don’t remember looking into but do know that we have a native SQL plug-in coming in V12; that will give more options.

Thanks Rick! But do these new options help rolling the database forward before it goes online? I’m just thinking from a bullet-proof point of view...

It sounds like what is needed is a CDP-like capability for a Database; I’d probably look into SQL replication if that’s the need.

A 1-minute RPO implies up to 1 minute of acceptable data loss.

I’d probably look into SQL replication if that’s the need.

No, that wasn’t the intention. The idea was to do a full restore of the server plus a database recovery to the latest point or a certain point after the last backup of the vm. And that all combined in one restore-process so that you don’t have to first restore the vm (and make sure that nobody else connects to it meanwhile) and then do the database restore…

Ah so with GA products, I would do the image-based restore, then Veeam Explorer restore to latest capture.

Hi @mcz,

The practice for a restore of an MSSQL server DB is as described by @MicoolPaul , shutdown or disable your applications\users and proceed with an IR restore VM server Database.

Alternatively, you could test this procedure.

1) Restore entire VM or IR with the MSSQL DB on board, network cards disconnected.
- check if SQL services start at boot.
- check if service startup account is domain will fail, possibly
change local user with Administrative permissions to star the MSSQL services.
- Connect via console to vm and use management studio to enter
locally without problems, because locally there is shared memory protocol.

2) Access the db via "SQL Server Management Studio and run the "alter database namedb set offline with rollback immediate" command.
Or lock the DB connection sessions, "Close existing connections to destination database" during the native DB restroe wizard

3) Reboot with network cards connected.

4) proceed with performing MSSQL point in time restore, only if the Database is NOT configured in "SQL Server Simple Recovery Model"...in this case you can only perform attach d or restore native format .bak

5) finished restoring Tlogs from Point in time put MS SQL DB back online

6) operational DB

Alternatively you can implement a stage MSSQL server with its advantages.

Configuring Staging SQL Server - Veeam Backup Explorers Guide

regards

Hi @mcz,

The practice for a restore of an MSSQL server DB is as described by @MicoolPaul , shutdown or disable your applications\users and proceed with an IR restore VM server Database.

Alternatively, you could test this procedure.

3) Reboot with network cards connected.

5) finished restoring Tlogs from Point in time put MS SQL DB back online

6) operational DB

Alternatively you can implement a stage MSSQL server with its advantages.

Configuring Staging SQL Server - Veeam Backup Explorers Guide

regards

Really great addition to what Michael said. 👍

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded