SQL Server Restore Additional Transaction Logs

[Nick Colebourn]

Hi, I have a customer with a requirement to restore a copy of a SQL Server database to another server to initialise an Always-On Availability Group. The source database is in the region of 3TB, with full backups running nightly and transaction log backups running every 15 minutes via VEEAM to meet the RPO of 15 minutes.

In this scenario, if we start the restore at 10:05am, and the latest restore point restore takes just over 1 hour to finish at 11:10am (With NORECOVERY), then I need to be able to apply the log backups from 10:15am, 10:30am, 10:45am and 11:00am to bring the transaction log up to date on the restored database so we can add the new server to the Always-On Availability Group.

As far as I can tell I can't get the individual log backups from the VEEAM SQL Explorer, and the log backups are stored as proprietary .vlb files so I can't extract them manually. Attempting to do another restore to the target server only gives the option to overwrite the database rather than append the extra logs, which means by the time it finishes we will then be a number of log backups behind again.

How can the above scenario of initialising a new Always-On Availability Group node from VEEAM backups be achieved when the time taken to restore the database to the new server is longer than the interval between new VEEAM log backups on the primary server?

Thanks

Nick

[PetrM]

Hi Nick and Welcome to Veeam R&D Forums!

This is one of the most prominent requests for our future releases but today there is no option to apply newly created log backups on the already restored database. As a workaround, I suggest considering Instant Database Recovery: the database is mounted directly from backup and is able to save all changes in the mount cache. The published database remains online while actual data is being restored in background. Once the restore is finished, differences between the published database and the restored database files are synchronized during switchover thanks to the mount cache.

Thanks!

[Nick Colebourn]

Thanks for the reply, it's appreciated. I'll have a look at the instant database restore to see how it works and if it can work in our scenario. From a feature perspective it'd be really great to be able to extract just logs from the VLB files, maybe added to the extract BAK functionality (possibly with scheduling). This would allow us to roll logs into already restored target databases for things like AOAG initialisation, log shipping, restore with standby to check states at a point in time scenarios etc.

Thanks again.

Nick

[Nick Colebourn]

Just as a follow up for this, Instant Restore is not a viable option for restoring databases to be joined to an Always-On Availability Group since it does not give you the option to leave the database in the "Restoring" state which is necessary for joining the database to the AOAG.

As such VEEAM is not capable of providing the functionality necessary for restoring a database to a new server to enable it to be joined to an AOAG, where log backups occur at a frequency that is quicker than the time taken to perform the restore in the traditional fashion using VEEAM.

We will have to revert to removing VEEAM, performing native backups for the AOAG expansion, and then reconfiguring VEEAM once done. This is less than optimal.

Thanks

Nick

[PetrM]

Hi Nick,

Many thanks for the provided information, so sorry to hear that there is no workaround besides running native backups and reconfigure our solution. Anyway, the request is valid and I hope to get some resources in future to deliver this functionality in one of our next releases.

Thanks!

[kaffeine]

We will have to revert to removing VEEAM, performing native backups for the AOAG expansion, and then reconfiguring VEEAM once done. This is less than optimal.

Wouldn't the new MSSQL Veeam Plugin be of aid in your scenario?

[PetrM]

Nope, the restore in plug-in works in the same way as explorer does, no option to append only logs.

Thanks!

[kaffeine]

I see, thanks for clarifying it.

Regards

[Nick Colebourn]

Just a follow up on this, we have come up with a scheduling workaround. The 3TB database takes approx 3 hours to restore, so before we perform the restore we will be modifying the backup job guest processing section to schedule the SQL Server log backups to take place every 300 minutes (5 hours). This waiting interval then takes effect after the next log backup. Once this 5 hour window is open we will then perform the restore normally with the latest logs, leaving the database in "Restoring" mode so it can be joined to the Availability Group. Once joined we will change the log backup schedule back to it's original settings which will pick up after the 5 hour window.

It leaves us with a risk of data loss if we have a DR event during the restore that impacts the log data not backed up, but it is slightly less trouble than removing and then reconfiguring the entire VEEAM setup from the servers which would then need a full snap of the whole system.

Still not ideal, but viable until the VEEAM software has the ability to roll forward additional logs built into it.

Cheers

Nick

[PetrM]

Hi Nick,

Useful info. It's good that you remember about such a serious drawback as a potential data loss but there is one more disadvantage: it violates the 15 min RPO requirement that the customer has.

It would be awesome if you could answer a couple of questions so that I can get a better idea:
1. Do I understand correctly that you can leave the DB in the "Restoring" state only once the 5 hours window is opened? As far as I understand, this "Restoring" state is the only reason why you need to change the log backup schedule
2. Why you cannot join the database directly to Always On as described on this page?

Thanks!

[Nick Colebourn]

Hi Petr,

1) The change in the log backup schedule is to prevent any log backups occuring on the primary server that will then truncate the log and cause a break in the log chain that will prevent the secondary database from being added to the AG.

2) Are we saying that if we specify it to be joined to the AG during the restore job configuration, and leave the original log backup schedule as it is, that VEEAM will not truncate the logs on the primary(during log backups) during the restore period on the seconday?

The key thing here is that log backups on the primary AG node(s) truncate the log meaning we don't have a complete log chain when a long running restore finishes. In this scenario this means we don't have a full log chain to allow the new secondary to catch up when joined to the AG.

For clarification here is a log chain scenario, simplified for the sake of space in this reply, with 2 hourly log backups and assuming we're adding a node to an existing AG.

Primary Node Full Backup (with log) - 2AM
Log Backup - 4AM
Log Backup - 6AM
Log Backup - 8AM
Restore Starts on new AG node at 8:05 AM, to the latest point so the 2,4,6, and 8AM backups. Expected to take 3 hours. Database must be left in the "Restoring" state so it can be joined to the AG.

Log Backup - 10AM (this then removes the log transactions on the primary node that were generated between the 8AM backup and the 10AM backup)

Restore Completes on the new node at 11:05AM. At this point we try to join the new node to the AG, but since the 10AM log backup removed all log transactions from 8AM to 10AM it is not possible to replay the logs from the primary node to the new secondary node since they no longer exist on the primary node. AG join fails due to this break in the log chain, and as it stands we cannot ask VEEAM to replay the extra log backup from 10AM.

In the example above to get around the issue we alter the log backup schedule so after the 8AM log backup it won't take another log backup until we've finished our restore and join process. Once we're done we alter the log backup schedule back to it's original timings.

[PetrM]

Hi Nick,

Ok, got it. My last idea to restore to Always On with explorer does not suit you because we'll restore the database to the primary node, then it will be replicated to other nodes. You have a different purpose: to leave the database in "Restoring" state, replay recent logs, and join it to the availability group.

Many thanks for the time spent on answering my questions, very useful and helped me to understand better the possible use cases.

Thanks!

[JGranden]

I'm on board with Nick on this. I figure i'll add my two cents as well.

I see three possible ways of handling this:

1. ability to export backup files -- if we could export the log backups as .trn files and full/diff backups as .bak files, this would allow lots of flexibility. (custom log shipping setups, restores to environments where veeam is unreachable, etc).

for example, we could export the full and log backups necessary to restore a db to a particular point in time without actually restoring the DB. this would be useful if the legal department wanted us to retain a backup of what a database looked like at a particular time and retain that backup for a longer period of time than our standard retention period.

another example, 3rd party replication products (such as QLIK replicate) occasionally need access to log backup files to re-initialize/re-sync replication. without access to .trn files, it pre-cludes using Veeam for SQL log backups. (fulls/diffs are fine though)

2. point-in-time restores should check for new logs to restore after the full completes. e.g. for Nick's example, before the restore finishes at 11am, veeam would check to see if there are new logs to be restored and adds them to the restore plan.

3. ability to restore individual log backups. specify an individual log to restore by its backup_finish_date from msdb.dbo.backupset.

[PetrM]

Hi Jesse,

Very helpful post, it's good to remember that there are different methods to solve this issue. I guess an officially supported tool for extracting log backups from our proprietary storage might also work, maybe it's not a "beautiful" fully automated way to solve it but already much better than nothing. However, I'd prefer to stick with the best usability whenever possible.

Thanks!