1

I have a large database. If I want to restore to a point in time using a full back up, it takes around 4 hours + time taken for transaction log restores.

I also have VM image instant restore snapshots taken on Azure daily. The restore of a VM image by replacing existing disks takes only 20 minutes.

It is known that transaction logs cannot be restored unless the most recent full back up is restored.

Lets say I sequence my backup jobs in this order: 1. Full SQL Backup of the database taken and stored on Azure using "SQL in Azure VM" feature of the Recovery vault 2. Azure VM image instant restore back up following it (there may be one hour in between with transactions occurring) 3. Transaction log backups using the same feature on Azure as #1, every 15 minutes

Then if we have an outage, can I do a faster restore by restoring the VM image? Would I be able to skip the full backup restore and directly restore only the transaction logs in this state of the VM? Is this a sound theory?

The LSN of the database should be at the state when the VM snapshot was taken. Would it be a problem since the database would come online immediately and the LSN would change before I can take it offline and do a restore?

Improve this question

1 Answer 1

Reset to default
2

Then if we have an outage, can I do a faster restore by restoring the VM image?

No. See my answer here. Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?

You can't attach a database without crash recovery, and you can't perform a crash recovery without the transaction log file. Log backups cannot be substituted.

If you put your database files on Azure Page blobs, instead of on an attached disk you can use File-Snapshot Backups for Database Files in Azure.

Improve this answer
4
  • what do you mean "Attach a database"? The database is already running on the server that has been snapshotted. When the VM goes down or the database goes bad - If I restore the VM snapshot, I get a database that is already functional. Also, I do have transaction logs on Azure (in the recovery vault, using the SQL in Azure VM backup feature). The question is - can I apply those transaction logs to the disk snapshots I have restored.
    – Prashanth Subramanian
    May 3, 2020 at 17:48
  • 1
    When you boot the VM snapshot SQL Server attaches the databases and runs crash recovery. At the end of crash recovery the database is "recovered", ie ONLINE, and you cannot apply transaction log backups to an ONLILNE database.
    – David Browne - Microsoft
    May 3, 2020 at 17:58
  • Thank you for clarifying. Based on your answer on the other thread we cannot just put an ONLINE database in RECOVERY mode and restore transaction logs. So the quickest way to restore a database to a point in time, available now is to do a full database restore + transaction log restores? Is there a scenario where we leverage snapshots to do a quicker restore? Since full restores take very long.
    – Prashanth Subramanian
    May 3, 2020 at 18:05
  • You can use snapshots if you put your database files in Azure files, as noted above. Also SAN solutions have some additional backup APIs they can use to accomplish this.
    – David Browne - Microsoft
    May 3, 2020 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.