Configure Veeam Backup & Replication for SQL Item Level Recovery

How to configure Veeam Backup and Replication 6.5 with an Application Group, a Virtual Lab, and a SureBackup job for Advanced SQL Item Level Recovery. I cover the Veeam SQL AIR Wizard for SQL Item Level Recovery from a backup. In this demo you will learn how to configure every aspect of your Veeam Backup & Replication configuration for seamless and non-intrusive backups and restores within your production environment.

---

Closed Caption:

this is joseph pipitone with pipitone
consulting and this video is going to
cover configuring veeam backup and
replication 6.5 with an application
group a virtual lab and a sure backup
job for advanced sequel item-level
recovery
ok so in this video we're going to cover
some advanced restoration techniques and
one in particular the sequel air wizard
and with this recovery wizard we can
restore database objects and tables or
we can execute custom query against the
database from the backup it's going to
allow us to restore specific data back
to the production sequel server database
or we can save it as a data file to
execute at a later time in order to
recover items from sequel we need to
configure a few things first let me show
you here in the backup job one of the
more important features that must be
configured here
what we want to make sure of in the
backup job is that you have
application-aware image processing
enabled this is going to allow us to
create a transactionally consistent
backup of virtual machine running a VSS
aware application or volume Shadow Copy
such applications are active directory
Microsoft's equal microsoft exchange and
sharepoint as well and this will allow
us to get the latest and greatest back
up without actually having to shut down
these services or the virtual machine
itself
this uses a vm proprietary technology
that enables us to ensure successful vm
recovery as well as proper recovery of
any and all applications without any
data loss so we want to make sure that
this is checked so I'm gonna cancel out
of this because i know that the backup
job has been running for quite some time
with that option enabled now what we
need to first do is click on this backup
infrastructure here and now in order to
create this virtual lab we need to have
an application group in this case we're
going to be working with sequel and
inside of an application group if i add
an application group here let's call
this sequel data recovery demo
application group ok an application
group is actually going to define the
virtual machines that are running
production applications there may be
other virtual machines that your
production application relies on such as
an active directory server if you're
working with exchange it would be
exchanged in that case so for these
purposes we want to make sure that when
the virtual machine boots up and in our
case the database server we want to make
sure that it can contact an active
directory domain controller or if we're
using a dhcp server for instance then
we're going to go ahead and add that
domain controller into this application
group now this is not required for a
sequel restoration
however I like to play it safe and I'm
going to add the domain controller to
this application group so when i'm ready
to add these virtual machines to this
application group i'll click this add vm
button now i have two options one is
from VI or from virtual infrastructure
and the other one is from backups I want
to make sure that since it is 430 and
people are still working i want to make
sure that I i put the least amount of
stress in our virtual environment as
possible and so i'm just going to use
the backups that we have first i'm going
to select the domain controller and add
that and then i'm going to select the
sequel server that we're going to be
restoring to and here's a role column
i'm going to specify that the Active
Directory server that i added is a dns
server domain controller and a global
catalog and i'm going to specify that
this database server is just a sequel
server and that's it for that step move
on to the next and click finish
now like I said before an active
directory server is not required when
you're restoring sequel and so the next
step is we need to create a virtual lab
and what a virtual lab is is an isolated
environment where we are actually
loading virtual machines into this
virtual lab we're creating a proxy and
so what that is
really means is our virtual lab
environment is going to communicate with
the production environment using a proxy
in between and we'll get into more of
that later on through this video let's
add a virtual lab and call this sequel
recovery demo virtual lab
and now we just need to specify a house
that we want this virtual lab to run on
so this virtual lab is going to consist
of any virtual machines that I specified
in the application group so i have an
active directory server and a sequel
server and they're both going to be
powered on in this virtual lab
environment which is completely isolated
from production
ok so let's pick let's pick one host
pick . 10 so when I create this virtual
lab it's going to create a resource pool
and inside that resource pool we will
see the proxy that beam is creating i'll
pick a data store that has a 1.9
terabytes free
that'll do
so this proxy appliance that were
configuring in the next step is going to
provide the team with access to all the
virtual machines running in the isolated
virtual lab and it's also going to let
these isolated virtual machines talk to
the production environment our
production Network happens to be vm
network and i'm going to configure an IP
address manually if you have a dhcp
server you can do that and that is also
another good reason to add your domain
controller if it is a dhcp server to
your application group but in our case
let's just configure a static IP get 95
another gateway is that 15 and our first
dns server is . 8 and our alternate is
that five so i know that when this proxy
comes up it's going to have an IP
address of that 95 and we're working
with a single subnet in this case now
with a basic automatic configuration via
will try to configure the entire virtual
lab itself but i like to have a little
bit more control so i'll pick manual and
you want to make sure that your vlan-id
is the same as your production network
and you can check that in vcenter here
click on the host networking
and here is our vm network so I'll view
the properties and right here vlan-id is
set to zero none so we're good there
back to the vm server
so now we're ready to configure the
v-neck which is going to let us connect
the isolated network to the proxy
appliance virtual machine and this is
going to allow network masquerading
which is going to assign an IP address
and it's going to masquerade that IP
address so we're going to be able to
access these virtual machines through
the proxy and so we'll go ahead and
click Edit so this IP address here that
we have to input is going to be the
default gateway of our production
Network which in this case is that 15
correct the network mask and now the
masquerade network address so if you
have a domain controller that is 192 168
. 0 . 10 for example then this is going
to masquerade the IP address as 192 168
that 255 . 10 now if we were using dhcp
we could manually input the name service
here but we're not going to utilize the
HCP since we are configuring a static IP
on the proxy appliance here we're just
shown a brief summary of the virtual lab
config what this is doing is creating a
resource pool and it's also creating a
virtual machine appliance which is going
to serve as the proxy it's also going to
create a virtual switch which allows the
production network to be isolated from
this virtual lab network
ok the virtual lab has been created I'll
go to vcenter and here you can see the
resource pool and here's the virtual
appliance which is our proxy so go back
to beam and click finish
we need to create a sure backup job
so sure
yup job is going to have an application
group associated with it and what this
job is going to do is aggregate all of
the settings and policies of a recovery
verification task such as an application
group and virtual lab to be used while
we recover from within this virtual
environment back to production so let's
call this backup job sequel recovery
demo here's our virtual lab that we
created and let's associate the sequel
data recovery demo with this sure backup
job now this is important we want to
keep this application group running once
the job completes this job will be an
on-demand job that will run as soon as
we make the request from the vm
enterprise manager if we check this box
here to keep the application group
running once the job completes will be
able to perform the item level restore
from our virtual lab environment over to
production if the application group
stops running
we will not be able to restore from the
isolated virtual lab back over to
production
and we don't need to run this
automatically its on demand and click
finish
so there we have it here so now that we
have this virtual lab environment setup
let's go back and take a look at the
database table here this just contains a
few records
let's go ahead and delete this table now
this is a production resource and i'm
deleting this table so this table is
gone from this database
what we'd like to do is restore this
table back from our backups that ran
last night back over into production
so let's go back over to the same server
and i'm going to start the sequel server
application item recovery wizard
this is going to allow us to specify the
sequel server that we would like to work
with and it will also request the
virtual lab
let's keep the duration at 30 minutes we
could always extend this if we run out
of time and the server name that we're
going to be working with is the database
server here
and the latest backup has the table that
i need and will click finish
now we should see something pop up down
here in the task bar that says that the
virtual lab is being requested and now
it's pending your team administrator can
do this via the veeam backup enterprise
manager will click on the request tab
and here is the lab request duration is
30 minutes and the state is pending
let's go ahead and approve this request
let's keep our defaults and this is the
virtual machine name that we have in our
backups and the backup server and how
many restore points we have I know last
night's backup contains that database
table that we previously deleted so i'll
just pick that one and click Next and
here is the sure backup job that we
created and the job state which is
currently stopped so let's go ahead and
select that sure backup job and click
finish and now that we've approved this
request we can go back to being backup
and replication and let's take a look
and here you can see down here the
virtual lab was approved and it says not
ready because the sure backup job has
not ran yet so once the sure backup job
runs it will essentially start these two
virtual machines up in an isolated
virtual lab environment and then we
could use the sequel wizard to actually
restore individual items back into that
database in this case a database table
so the sure backup job started let's
take a look at the statistics and we
could see exactly what's happening here
it looks like it's starting the domain
controller and it will actually publish
the domain controller with a unique name
what it's actually doing is it starting
the proxy appliance first as you can see
here it's powered on
and then what it's going to do is it's
going to create the domain controller
and the database server that I have
configured in the application group and
it's going to create those as virtual
machines in our cluster however they're
going to have unique names and after it
starts the virtual machine it will wait
and it will perform a heartbeat test and
a pink test just to make sure that
everything is a-ok and it will be able
to determine whether or not it's able to
communicate through our proxy here you
can see that being created a virtual
machine with a different unique ID here
this way we don't confuse it with our
production resource here we can see as
it's going through its test here it
looks like the heartbeat status was a
success the pain was success and we're
not running any scripts and so what it's
going to do is move on to the next
server which is our database server ok
looks like everything past with success
and now it's moving on to the database
server ok looks like we have some pretty
good pass rates here success on all
counts and we have one last test to go
so it looks like our lab is ready to go
and we could launch the sequel recovery
wizard two different ways here we could
click on the database server in our
virtual lab and we could right click and
go to sequel item recovery
you can also go down here to the virtual
lab manager and we could scroll down to
see that the virtual lab is ready for
another 28 minutes and we can just click
on open but for simplicity's sake i'm
just going to launch the sequel item
recovery by right-clicking and selecting
that option here and now here is the
backup server name and this is the
masqueraded IP address of the backup
server which is completely isolated from
our production environment and the only
way that it can talk to production is if
it goes through the proxy that we
created in an earlier step production
server name and the authentication we're
just going to use windows authentication
now if you don't have a domain
controller as part of your application
group this will still work as long as
your credentials are cached on that
database server we are presented with
three different options here we can
restore the database schema
could restore the database tables or we
could restore a sequel query result
in this demonstration let's just restore
the database tables so the next step
here is to select the restore target in
this case we want to restore it back to
our production database and let's pick
the backup database name which is being
temp and the production database is also
being temp and this step of the wizard
is going to retrieve data and compare
the sequel server and the virtual lab
environment to the production
environment
and this gives you the option to
customize naming which we are not going
to do we want the table to go back with
its original name so click Next and this
allows you to select different file
groups in our case I'm just going to
preserve the file group and the
partition table click next and when i
click restore it is restoring the table
back to production and it looks like it
was successful
so let's go back to our database server
and let's do a refresh and there's the
table back in production and so we are
finished up with that part of the
restoration so i'm going to go back to
the vm server click finish and we're
done with this virtual lab that was the
only item that I needed to restore if
there were other tables i could go ahead
and continue on but in this case I just
wanted to show you how we can use this
sequel air wizard to restore individual
objects back to production so as soon as
I click stop session it's going to
essentially shut the entire virtual lab
down and because we're all finished with
it and it will clean up the redo logs on
register the virtual machines and
unpublished them and that's it
this virtual lab is ready to go if we
needed to perform anymore restorations
in the future if your organization needs
any assistance setting up this
functionality within veeam backup and
replication feel free to contact us at
pipitone . co