1

Working with an interesting scenario that quite frankly, I have not had the time to test, and would love some expert insight. I have a question concerning transaction logs, and their possible use for recovery (or anything at all for that matter). Imagine that you are using VSS to take application consistent snapshots of volumes holding both .mdf and .ldf files for the database (storage array driven). The questions I have is:

Given that only VSS snapshots are available (point in time), I am wondering if there is any use in having a database in full recovery (assuming there was a way of truncating the transaction logs as well). So, the setup is: database is in full recovery -> VSS snapshot of database -> truncate transaction logs. Can the transactions in the log be of any use to me if I were to mount a clone of the database?

2

I would 'suggest' that VSS snapshots are 'mostly' intended for an out-and-out disaster restore scenario. It is my understanding that VSS snapshots allow you to restore to that snapshot time. No time just before snapshot and no time just after the snapshot (until the next snapshot).

What are you going to do when an accidental delete occurs due to user error? What happens 10 minutes after your VSS snapshot is taken and the server dies or a disk crashes. What about corruption that hits 10 minutes after your VSS snapshot?

There is tremendous benefit in having your databases in FULL recovery mode and taking timely transaction log backups (even down to the minute)

Honestly, this gets down to your company RPO (Recovery Point Objective) and RTO (Recovery Time Objective). Are YOU the one that can actually decide these things?

1

No matter how you take the backup, the usual rules of RTO and RPO still apply and should be the main driver of your backup strategy. To answer your question: if your RPO is 10 minutes, unless you take a full backup every 10 minutes (I hope not), you still need database in FULL recovery and take a log backup every 10 minutes. The transaction logs are of use for SQL server and unless you back them up, they are of no use for you.

For the real application-aware VSS snapshots, the log will also be backed up and truncated and the appropriate event logged in the msdb. This is done via SQL VDI (Virtual Device Interface) and most 3rd party backup tools use this interface. This has been specifically designed to interface with 3rd party backup tools.

Take a look at SQL Server Admin Guide for Veritas Backups. It explains how it works in depth: https://www.veritas.com/support/en_US/article.DOC8593

Storage (SAN) based snapshots are completely different and you do not get the ".bak" file as these are at the block level and thus SQL is unaware of SAN snapshots. In order to maintain integrity, both .mdf and .ldf must be in the same consistency group but each vendor is slightly different in this approach.

Keep in mind for VSS based backup is that the provider will freeze the IO in order to flush the data to disk but the subsequent backup file is written to from snapshot.

Some more useful resources on this subject:

https://blogs.msdn.microsoft.com/psssql/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources/

https://support.microsoft.com/en-gb/kb/919023

I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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