Is VM Restore ok if you don't need point in time db restore?

[Peter Provant]

Hello,

Our serveradmins have implemented Veeam backup to backup our servers.
In addition they have implemented Veeam backup for SQL Server databases.
So now, we (the DBA-team) no longer have to make backups using TSQL and we can execute point in time restores using Veeam.

We have an application for which a "special" backupscenario has been created :
1) stop application services
2) make veaam backup of the sqlserver
3) make veaam backup of the applicationserver that contains a disk with application files (being refered in the database)
4) start application services

This approach ensures the backup contains a filesystem that is 100% in sync with the databasecontent.

Multiple times a year new releases of the application are deployed.
Before the deploy we check whether the backup succeeded.
So, in case something goes wrong during the deploy, we can go back to the pre-deploy backup.

Now I wonder whether it is safe to restore the virtual machines (application server and database server)
instead of restoring the application server VM and restoring the SQL user databases using Veeam for SQL Server.

I have to add we do not stop the sql server services duing the "special" backupscenario, only the services on the application server.
So, I wonder we might get database inconsistencies if we restore the SQL Server VM instead of the SQL Server databases.
Since the database services are not stopped during the "special" backup there is a (very) small chance that a database transaction,
a checkpoint or another sql server system task is in progress while the Sql server VM is being backup.

I assume it's enough to restore the SQL Server VM.
I assume (as should happen when restarting after a sql server crash) transactions that have not been committed yet will be rolled backed, and committed transactions
that haven't been persisted in the datafiles will be rolled forward after restoring the SQL Server VM.

Is my assumption correct or do I risk (worst case scenario) ending up with database inconsistencies or suspect pages if I use virtual machine restore
instead of database restore in this scenario?
Has anyone tested starting a long running database transaction, taking a virtual machine backup while the transaction is in progress and than restoring the virtual machine?

Thanks + kind regards
Peter.

[foggy]

Hi Peter, I'd assume you're fine restoring the entire SQL Server VM, since the application is stopped anyway and doesn't perform any activity and SQL itself is properly quiesced during VSS-aware backup (though it is pretty much able to restore even from a crash-consistent backup).

[Peter Provant]

Thank you Foggy.
Since it's about one of our critical applications, I just have to be 100% sure. If it's only 99% sure this will not give issues on SQL Server level, I prefer to use the SQL restore.
Once we had some networkissues causing a SQLServer loosing his disk and that caused some database problems. Off course in that scenario IO was not freezed by VSS.
Anyway if it ever should be necessary we can restore the virtual machine and then we could still restore the databases in case there should be an issue.
I also wonder if there might be some "clock" issues. I don't know how OS and SQL interact with actual time when you go back to eg 20 hours earlier. For sure we'll lose all entries that have been written in win event logs and sql server logs, but I don't see an issue in that.

Kind regards
Peter