Backup restores into a replica

[philfreund]

We are considering a DR scenario where we create a replica of a SQL Server server once per day from a backup copy but do backups (non Application aware) of that server multiple times per day to be able to limit data loss. In this scenario, the replica would be used as-is only if management decides we have to get the applications up immediately and that a 1 day data loss is acceptable. Otherwise, we would take the time to restore the latest database backups stored on the latest Veeam backup copy onto the now failed over replica and roll the database forward. Is there any problem with doing this? Does this make any sense or am I complicating things unnecessarily?

[foggy]

Hi Phil, technically this should be possible. Why do you do non-AAIP backups, btw?

[philfreund]

Using the AAIP backups complicates my ability to restore the database backups to our testing and DR environments which I have to do monthly. I can just copy the regular SQL server backup files to the test and dev servers and do the db restores into the appropriate databases. I couldn't see a simple way to do the same thing using AAIP. Plus several of my SQL Server databases exceed the 10 GB max for SQL Server Express and I don't have enough SQL Server licenses to use another SQL Server to be the staging recovery server using AAIP.

[foggy]

Using the AAIP backups complicates my ability to restore the database backups to our testing and DR environments which I have to do monthly.

1. You can do AAIP backups with the copy_only setting, thus leaving logs intact (if this is your concern).
2. You can consider using SureBackup for that, since it was designed with this use case in mind as well.

[philfreund]

I have been thinking about this and am mostly convinced that your suggestion would work for us. I do have a couple of questions though.
1. With AAIP enabled, is a database full backup created every time the main job is run?
2. Is there a way to control the location where the database backups and tran log backups get stored?
3. If I do copy_only, do I just run a SQL Server job to do periodic cleanup of the db backups and log files?

[veremin]

1. Correct.
2. Correct, this location is called backup repository.
3. If you do copy_only backups and still want to backup logs periodically, you will have to use another application for that goal (SQL native tools might help here).

[philfreund]

OK. Just to make sure I understand correctly, there are no SQL Server .bak files created but all the databases on the VM are application consistent. Thus to do a restore of a specific full backup, you have to use the Veeam Explorer for SQL Server and point it to the correct restore point; you can't use SQL native tool to restore from a .bak file.

[veremin]

Right, there are no .bak files. To store backed up SQL logs VB&R use .vlb files. These files are used to guarantee SQL db point-in-time recovery. Also, these files are only created, if corresponding option (backup logs periodically) is enabled.

Otherwise (if AAIP is enabled, but other SQL processing options are set), you will only be able to recover db to the time when VM backup was taken, and, then, use SQL native tools to replay logs to specific transaction. The db will be in consistent state (thanks be AAIP technology).

Thanks.