by philfreund » Thu Jan 19, 2017 4:23 pm people like this post
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?
by philfreund » Thu Jan 19, 2017 5:00 pm people like this post
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.
by foggy » Mon Jan 23, 2017 3:14 pm people like this post
philfreund wrote: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.
by philfreund » Tue Jan 31, 2017 7:31 pm people like this post
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?
by v.Eremin » Wed Feb 01, 2017 10:15 am people like this post
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).
by philfreund » Wed Feb 01, 2017 1:25 pm people like this post
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.
by v.Eremin » Thu Feb 02, 2017 12:56 pm people like this post
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).