Backup restores into a replica

Availability for the Always-On Enterprise

Backup restores into a replica

Veeam Logoby philfreund » Thu Jan 19, 2017 4:23 pm

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?
philfreund
Influencer
 
Posts: 16
Liked: 2 times
Joined: Fri Apr 01, 2016 6:16 pm
Full Name: Phil Freund

Re: Backup restores into a replica

Veeam Logoby foggy » Thu Jan 19, 2017 4:45 pm

Hi Phil, technically this should be possible. Why do you do non-AAIP backups, btw?
foggy
Veeam Software
 
Posts: 13923
Liked: 1013 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backup restores into a replica

Veeam Logoby philfreund » Thu Jan 19, 2017 5:00 pm

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.
philfreund
Influencer
 
Posts: 16
Liked: 2 times
Joined: Fri Apr 01, 2016 6:16 pm
Full Name: Phil Freund

Re: Backup restores into a replica

Veeam Logoby foggy » Mon Jan 23, 2017 3:14 pm

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.
foggy
Veeam Software
 
Posts: 13923
Liked: 1013 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

Re: Backup restores into a replica

Veeam Logoby philfreund » Tue Jan 31, 2017 7:31 pm

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?
philfreund
Influencer
 
Posts: 16
Liked: 2 times
Joined: Fri Apr 01, 2016 6:16 pm
Full Name: Phil Freund

Re: Backup restores into a replica

Veeam Logoby v.Eremin » Wed Feb 01, 2017 10:15 am

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).
v.Eremin
Product Manager
 
Posts: 12397
Liked: 892 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Backup restores into a replica

Veeam Logoby philfreund » Wed Feb 01, 2017 1:25 pm

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.
philfreund
Influencer
 
Posts: 16
Liked: 2 times
Joined: Fri Apr 01, 2016 6:16 pm
Full Name: Phil Freund

Re: Backup restores into a replica

Veeam Logoby v.Eremin » Thu Feb 02, 2017 12:56 pm

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.
v.Eremin
Product Manager
 
Posts: 12397
Liked: 892 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Google [Bot], kjm1979, Majestic-12 [Bot], MSNbot Media and 14 guests