VSS deleting Logs ?

[Titanmike]

I just saw something in the logs

Information Store (1588) Archive: Deleting log files L:\Exchange\Archive\E010000002A.log to L:\Exchange\Archive\E0100000041.log.

I am confused, the option to Truncate the logs should surely just remove the empty bits rather than deleting the logs altogether ?
In this case I backed up an Exchange server and in this case I would lose the option to restore a specific point in time ?

[Gostev]

Hi, I am not sure where do you see the issue here. What you are observing is Exchange removing transaction logs that are no longer required to be able to roll forward from the last successful backup.

[Titanmike]

Hi

OK lets leave Exchange for a minute as I think that might be different.

With regards to SQL Server - it looks like Veeam makes no attempt to backup transaction log data, nor does it remove any transaction log data (this is good btw, we don't want Veeam playing around with our transaction log data; this is needed for point in time restores).

So what does "Truncate Logs" do on a SQL Server box ?

[Gostev]

It also removes all transaction logs after successful backup. If you need point-in-time restores, then just do not enable transaction log processing on your SQL Servers.

What do you mean by "Veeam makes no attempt to backup transaction log data"? Logs are getting backed up as a part of VM image. I guess you are thinking in terms of legacy backup approach, when transaction logs were backed up separately.

[Titanmike]

It also removes all transaction logs after successful backup. If you need point-in-time restores, then just do not enable transaction log processing on your SQL Servers.

What do you mean by "Veeam makes no attempt to backup transaction log data"? Logs are getting backed up as a part of VM image. I guess you are thinking in terms of legacy backup approach, when transaction logs were backed up separately.

We performed a test

1. Create a new database “test”
2. Create a new table in the database
3. Perform a backup
4. Insert thousands and thousands of rows into the table
5. See how much data is present in the transaction log datafile.
6. Perform a backup
7. See how much data is present in the transaction log datafile.
8. Backup the transaction log via SQL Studio

5 and 7 are the same, so the transaction logs were not cleared. In addition the backup of the log at step 8 was the full amount of data in the transaction log.

[Gostev]

Are you using v5 and have app-aware processing enabled for this VM, with option to truncate logs? If yes, we would need to investigate the log files for both backup job, and our guest agent (log from SQL VM) to determine why transaction logs did not clear. Thanks!

[Titanmike]

yes, latest version. Will perform more tests and open a ticket ...

[Titanmike]

Hello Gostev, my name is Lee, I'm a DBA here at Titan.

The question I'd ask is this: If you were backing up a SQL Server VM every 4 hours say, then the customer got in touch and wanted a point in time restore, I see no way of achieving this using the veeam backups. You can only restore the VM back to one of the 4 hour backups, not in between.

I am perfectly fine with this and frankly the approach should be to use veeam for system images, and other tools for application (brick level) backups. However what concerns me is that it sounds like you are expecting to clear the transaction logs following a veeam backup, which is not right.

[Gostev]

Hi Lee,

You are right, point-in-time restores are not possible with full VM restore. However, you should be able to perform file level restore of MDF file from latest backup, and then apply new transaction logs on top of that for point-in-time restore. I have not tested this myself (and I am not big SQL guy anyway), but I am sure you know best what can and cannot be done with SQL for restore.

No, we definitely do not expect customers who are intentionally using full recovery mode for point-in-time restores to prune logs after successful backup. However, while I am sure this will be totally surprising for you, there are quite a few customers (especially in small business, where there are no designated DBAs, but rather do-it-all administrators) who are using full recovery model just because it is default model for new databases starting SQL 2005. They do not need point-in-time recovery (and do not even realize this functionality exist), they are just fine restoring previous full SQL VM backup with some data loss, which is typically perfectly okay for their business. Of course, the proper solution for anyone who does not need point-in-time restores is to just switch databases to simple recovery model. But this is only obvious if you know Microsoft SQL and many do not.

Now, because these customers are not doing anything about their transaction logs (again, in most cases do not even know these logs exist), the logs just grow forever, fill up all disk space, and start causing very real issues to production database. Then, they would typically do some Googling, or ask customers on various forums, and often people will them that logs are truncated after backup only. And so they come to us opening severity 1 support cases and blaming our product for not truncating those logs. Nothing pretty in general, and while the fix is easy (simple recovery model), downtime did happen.

So, to prevent this for happening in future, we have built the capability to truncate those logs into our product. Supposedly, this should make everyone happy now... those who really need full recovery model for point-in-time restore will not want to use log pruning, as you correctly stated. And people with little SQL knowledge will get their logs truncated, thus keeping production VM healthy.

I hope this answers your question? If you have any suggestions on how we could improve image-level backup for SQL servers on top of what we currently have, I will be interested to hear!

Thanks!

[Titanmike]

Hi Gostev

I've been playing around trying to perform a point-in-time restore, and the answer is that it is not possible. SQL Server requires that a restore starts with a full .bak restore and then subsequent .trn restores.

Restoring file-level backups from veeam and then trying to take a full .bak backup, swapping around with datafiles etc; just doesn't work.

This is all fine though, because my original understanding was that application level backups should be done using appropriate tools; veeam is simply a system image backup.

What remains is the discussion about transaction logs. Currently the logs do NOT get cleared, but you imply they should (with the option to truncate logs set). I'm not sure how you plan on achieving this; the only way I know of removing transaction log data without performing a transaction log backup is to change the recovery model to Simple and then back to Full.

For what its worth although I agree with your point that some customers end up filling their disks with transaction logs, I don't think this is your problem to fix with Veeam. You should never change the recovery model on a database or muck about with transaction log data. If a customer fills their disk and doesn't run transaction log backups, get them to change the recovery model to Simple. If they complain, point them to a suitable SQL Server book or training course !

[Gostev]

Hi, no - our way of doing this definitely does not involve touching any database settings (such as recovery model). We simply emulate log backup, so that SQL server thinks logs were actually backed up.

Anyway, I just found out there is a bug that prevents this from working on default instance which is what you are probably using for your testing.

[Titanmike]

OK thats good to know.

My opinion is that "Truncate Logs" should NOT be the default option because you then remove the ability to do a point in time restore.

I guess your argument is that if it is not the default option users will fill their disks. So you are caught between a rock and a hard place.

As this option has significant impact in an enterprise environment, perhaps it should be a question that is asked by the wizard when creating a job? So that you have to pick an option and therefore understand the implications.

Anyhow - we will configure our jobs to NOT truncate logs as we handle Exchange/SQL backups via brick-level backup tools.

Kind Regards
Lee.

[Gostev]

But why not do log truncation for Exchange? Exchange is the whole different story comparing to SQL, recovery concept is different. Moreover, log purging is handled by Exchange VSS writer itself (we do not need to emulate log backups or anything - we just need to signal VSS that our backup was successful, and Exchange will handle proper log processing automatically). Actually, for Exchange, we had log truncation functionaity since v2, and you could not even disable it until v5, so every single customer was effectively doing this. If you search this forum, there are dozens of topics talking about log truncation for Exchange, and Exchange point-in-time restores from Veeam backups. Everyone is happy (except with the fact that before v5, logs were truncated immediately after snapshot, not after backup actually completes successfully - which was addressed with v5).

v5 only added log truncation for SQL via this workaround (to address this specific scenario I have described). Workaround is needed because SQL VSS writer does not handle log processing, unlike Exchange VSS writer. May be you are right, and we should hide it behind registry key for SQL server. Actually log truncation is disabled by default, but it is enabled automatically if you turn on application-aware processing.

BTW, thanks to this topic I got new challenge right now, doing some research to see how we could possibly provide SQL point-in-time restores from our image-level backup. Nothing is impossible.

[Titanmike]

Yes I agree that Exchange should be treated differently. For a start Exchange offers the "Deleted Items" to each user, so users can undo their own mistakes You would not roll back an entire Organisation's information store just because one user needed to retrieve some old content in a mailbox. Exchange backup (using Microsoft tools) is more about disaster recovery. Of course, most organisations would use third party software to run brick-level backups, but thats a different story.

SQL Server is different (especially in a hosted model - we are a hosting company with potentially hundreds of databases on a large shared SQL Server) - we have the need to restore an individual customer database without affecting any others. Your point about restoring to a snapshot point is very valid; its very rare we perform point in time restores for customers, that work is typically for much larger customers who have dedicated SQL boxes to themselves.

Our backup strategy is always to use imaging software (Acronis, Veeam or even vmware snapshots) for disaster or bare-metal recovery, and to use application tools to backup data (mysqldump, SQL built-in backup/restore, Acronis for Exchange etc).

The problem you will face with SQL Server restores is that you only have image-based backup data to work with; SQL requires its built-in backups to .bak and .trn files in order to perform point in time restores.

[Gostev]

The problem you will face with SQL Server restores is that you only have image-based backup data to work with; SQL requires its built-in backups to .bak and .trn files in order to perform point in time restores.

What if we backup the SQL transaction log locally (to a file on the same SQL VM) before we actually create backup of VM? I read you can do point-in-time restores with just MDF, LDF and TRN - as long as database is restored with NORECOVERY. Will that work?

[Titanmike]

Thats getting a bit messy. We have had plenty of customers where their transaction logs have grown so large that there isn't room on disk for the log, plus a backup of the log. We always backup the log off the server to an iSCSI drive.

I have only ever performed restores with conventional ".bak" and ".trn" backups being available. In this scenario you restore the ".bak" file first and specify NORECOVERY such that transaction log data can be applied. I don't know if it is possible to start with a basic .mdf/.ldf database, somehow "force" it into recovery mode and then apply transaction logs. I suspect not.

[Gostev]

Absolutely no problems for us backing up TRN to any storage available to Veeam Backup server (instead of locally to VM) if needed - for example, to this iSCSI drive you are using today. I guess I need to research more on NORECOVERY key and if it can be used somehow during full VM image recovery.

[jont]

Gostev,

You mentioned that the default SQL instance doesn't purge the transaction logs after a backup job is completed. Will this be fixed in an updated release of V5?

Jon

[Gostev]

Hello Jon, no unfortunately this was too late to fix in 5.0.1 - we decided to provide a hotfix for 5.0.1 instead. Thanks!

[tsightler]

Absolutely no problems for us backing up TRN to any storage available to Veeam Backup server (instead of locally to VM) if needed - for example, to this iSCSI drive you are using today. I guess I need to research more on NORECOVERY key and if it can be used somehow during full VM image recovery.

I think there is a VSS API call when performing a VSS restore that will allow the instance to be left in "NORECOVERY" mode so that transaction log backups can be applied.

I also believe you can also still perform a point-in-time transaction log restore even without this by leveraging file level restores and a MSSQL tail-log backup. I've been meaning to try this but just haven't had the time. In theory it should work because it's no different than performing a tail-log backup and restoring a point in time after an MDF file crash.

[Gostev]

I think there is a VSS API call when performing a VSS restore that will allow the instance to be left in "NORECOVERY" mode so that transaction log backups can be applied.

Yep, I have already tasked the corresponding developer to do this research on VSS API call when he gets a moment. Actually, I have already found everything needed (or so I believe) last week, now just need devs to spend some time to actually try to implement test utility and try to use it with SQL to see if it works.

I also believe you can also still perform a point-in-time transaction log restore even without this by leveraging file level restores and a MSSQL tail-log backup. I've been meaning to try this but just haven't had the time. In theory it should work because it's no different than performing a tail-log backup and restoring a point in time after an MDF file crash.

This sounds promising! Maybe someone else will want to test this as well. Someone with sufficient SQL administration knowledge (I am not one of those hehe).

[Davd]

Hello Jon, no unfortunately this was too late to fix in 5.0.1 - we decided to provide a hotfix for 5.0.1 instead. Thanks!

Gostev: I had a ticket with support about the issue with SQL log truncate so I'm waiting for a hotfix... Are you saying that update 5.0.1 is already released and that there is a hotfix for 5.0.1 that solves the log truncate problem already available? Do I contact support to get download link for 5.0.1 and the hotfix?

[Gostev]

Hello, 5.0.1 will be public release, when released (withing next few days) it will be available for download through our website, and existing customers will get notificiations. Once this is out, we will produce the hotfix for this issue that you will be able to install on top of 5.0.1. Thank you!

[jgriffin.01810]

Is the SQL log hotfix available? If yes, can you tell me how to obtain it?

Thanks,
Jeff

[Vitaliy S.]

Jeff,

All hotfixes are distributed through our technical support team, so to obtain (or be notified on hotfix availability) you should simply open a support ticket. You need to have active maintenance agreement in force to be eligible for product updates.

Thanks!