Replicating/Copying EC2 MSSQL backups to on-prem

[mike7891]

Hello, I am looking for some guidance on a way to copy backups of an EC2 server with MS SQL Server installed on it from an S3 backup repository to a local repository. Currently we have Backup & Replication installed at our on premise location and need to pull these EC2 backups down to perform database level restores to a SQL server on premise. So the backups need to be SQL-aware and also handle transaction logs. Our on premise repository is a hardened Linux repository.

I have reviewed the following potential options, but unsure which would be a good fit:

1. Veeam for AWS - Appears to support Backup Copy from S3 => Onprem repository. I could not conclude however if it supports SQL transaction log backups as this is a requirement.
2. Veeam B&R agent installed on the EC2 instance. My understanding is the agents support transaction log backups for SQL. We would prefer the EC2 agent backups go to S3 and then copy them down to our local repo. However, I'm unsure if it is possible to perform a backup copy from S3 to On-prem using this method.

Any guidance would be appreciated on how this goal can be achieved. Thank you.

[nielsengelen]

Hi,

Veeam Backup for AWS doesn't allow creating transaction log backups but does allow for in-guest scripts that could handle this.

The agents do support transaction log backups, however as listed in the user guide - it is not possible as of today to copy those backups.

Another option would be to use VBR and the MSSQL plugin to create backups supporting backup copy jobs (for more information, see the user guide).

[mike7891]

Understood, thank you for clarifying. I think Veeam Backup for AWS is still of interest to us as we may be able to do an application restore of our SQL Server from AWS to on-premise VMWare VMSphere from this guide here. We can look into handling transaction logs in a different way. But in terms of application level restore, would the following work?

1. Take application consistent backup of EC2 SQL instance using Veeam Backup for AWS and store into S3
2. Backup copy those from S3 to our on prem repository using on prem Veeam Backup and Replication
3. Initiate application level restore to a local VMWare VM to restore SQL server databases from Veeam Backup and Replication

[nielsengelen]

This looks like the correct flow.

[mike7891]

Thanks! Is it possible to deploy the appliance, workers and S3 under the same account that our EC2 instances are in? We would like to do this with the least amount of accounts as possible, but still be able to do the above and also configure retention policies on the snapshots and image backups. Reason behind consolidating under one account is for billing purposes and we intend to only use it for this one account and a few EC2 instances. Just checking if this configuration is possible, or if separate accounts need to be made as per this guide: https://helpcenter.veeam.com/docs/vbaws ... tml?ver=80

[nielsengelen]

This is possible. We just recommend to have a backup account for an extra layer of security.

[mike7891]

Thanks. I’ve finished deployment and have ran a backup copy job to bring my EC2 image backup to our on prem repo. I tried a restore of a SQL database using the application restore feature and it wants a staging server. Is this required? I thought a staging server was only needed for point in time transaction log restores. Is there any other way to restore my SQL database from EC2 instance that doesn’t require a staging server? I am just looking to restore full databases.

[nielsengelen]

To clarify, where did you try to restore to? Back to AWS or was it on-prem in an infrastructure?

[mike7891]

Trying to restore the EC2 SQL database to on prem. I ended up putting in a support ticket and it sounds like the staging server is required for this.