Script Nightly DB Restores

[Grime121]

We have to restore DBs from our PROD environment to our CLONE environment on a regular basis. We have been unable to figure out how to script these SQL DB restores using the Veeam PROD server backups, so have instead been having to take separate SQL backups using the Data Domain DDBoost MS SQL server plugin. This results in us having to store multiple backups of the same DBs though, which uses up space unnecessarily.

Does anyone know how we can script DB restores using the Veeam PROD SQL server backups as the source, and our CLONE environment SQL servers as the target?

I still can’t believe the entire Veeam Management Console has to be installed, in order to use the Veeam PowerShell modules. Why has this not been addressed yet? Surely other Veeam users are equally as frustrated by this as we are. Does anyone know if a feature request has been submitted regarding this?

[HannesK]

Hello,
correct, the console has to be installed to use the PowerShell module. Forum search offers unsupported workarounds. Yes, the feature request is open and same like any other missing feature the reason is "a lack of resources".

About the restore: how IO intensive is your cloned environment? Would it make sense to use "publish" or "instant recovery" (no disk space needed in both cases) instead of a full restore?

Can you maybe post the script you used for restore to have a start? Forum search for Restore-VESQLDatabase, also returns some code sniplets that should help.

Best regards,
Hannes

[Grime121]

The DB restores to the CLONE environment are nightly in some cases. Weekly in others. It is just simply done to provide the developers with an environment that is similar to PROD, that they can use for testing purposes. It is not IO intensive. The DBs are only accessed when they need to perform regression testing.

I’m not sure what you mean regarding “publish”, or “instant recovery”, rather than simply restoring the DBs. We cannot restore the entire PROD VM as the CLONE instance, if that’s what you mean. The CLONE VMs have custom configurations, etc. We need to restore specific SQL DBs from the PROD VM backups. Restoring the entire VM, or even the entire disk that contains the SQL DB files will not work.

[HannesK]

yep, that use case is done by many customers with scripts (restore or publish, depending on the situation)

With instant recovery / publish, the backup repository is mounted directly to the SQL server as disk. Then the database can be used after a few minutes no matter how large it is. If you know virtual machine instant recovery, then this is a similar concept just for databases. That saves time and disk space. It works fine as long as it is not too IO intensive. Maybe trying it out in the GUI is explaining it more
https://helpcenter.veeam.com/docs/backu ... shing.html
https://helpcenter.veeam.com/docs/backu ... shing.html

I'm only talking about databases. I'm not talking about restoring a VM or disk.

[Grime121]

I don’t quite follow… if I mounted the backups as disks on the CLONE servers, what would happen to the backed up data, if changes to the DBs were made in the CLONE environment? We want the Developers to be able to make changes to the data in the CLONE environment. We just want them to have a fresh instance of the PROD data to work with each day. We cannot modify the data in the backups, though. Otherwise, if we ended up having to use those backups to restore PROD, we would have changes included in that data that were made in CLONE, right? Or does any data you change when you Mount those backups get saved elsewhere, and the actual backup data does not get modified?

Basically I am trying to say that we need these PROD backups for not only PROD restores, should that ever be required, but we also need to use the nightly PROD DB backups in CLONE, and be able to modify that data without if effecting the actual backups.

We don’t have any problem with running a DB restore job each night, to restore the backed up PROD DBs to the CLONE SQL servers. I just don’t know how to script that. Of course, if we could save disk space by not having two different instances of that PROD data, that would be even better. However, the Developers might need the performance of the CLONE environment to match that of the PROD environment, for testing purposes. In that case, we would definitely not be able to use the option(s) that you are suggesting. Our SAN is fully capable of dedupe and compression though, so I would think that the dedupe capabilities would prevent a ton of extra space from being used up by the CLONE DB instances of the PROD DBs.

[HannesK]

what would happen to the backed up data,

backups always stay the same. It's a backup - and backups never change Changes are saved in a cache on the mount server (the repository if you kept the defaults)

For scripting, I would start with the examples provided in helpcenter (if you scroll down, there are examples on each command) or use forum search for existing scripts (as mentioned above).

[c.evans]

This script I posted a few months back might be a solution for you. If nothing else, it's a jumping off point and you can tweak it to your own needs and not have to develop everything from scratch.

Hope it helps!

https://community.veeam.com/script-libr ... p-job-1103

[Grime121]

Thanks. That looks helpful.

One issue that I didn’t consider previously is where our backups are stored. Our PROD servers are in a datacenter in Atlanta, GA, and the daily backups are stored on a local Dell SAN. Our CLONE servers are located in a datacenter in Plano, TX.

The Dell SAN at each location does replicate to the other, so those daily PROD backups would eventually be available on the Plano SAN. We have a dedicate 1Gbit link between the two datacenters, too.

I guess I’m just wondering if I should go through the trouble of attempting to mount the backups from the replicated data (not sure how I would determine when the replication finishes, since it is done at the SAN level), or if I should go through the much larger trouble of moving the CLONE servers to the Atlanta datacenter.

Those are the only two options I can think of. I don’t think we would want to mount the backups directly across the 1Gbit datacenter connect link…. Does anyone have any other suggestions? If not, how difficult would it be to mount the backups from the replicated datastore on the SAN, and how could I determine when the replication of that data had completed, so that I know when I can proceed with the mounting of that backup data? Would I just need to get a rough estimate of how long it typically takes to complete, and then add some additional time to that, to make sure the replicated backup data is fully available before I attempt to mount it? I suppose worst case I could mount the backup from 2 days prior. Would anything change when a full backup runs vs. an incremental? I guess it would if I was mounting the most recent backup (replication would take longer), but it wouldn’t if I mounted the second oldest backup, right?

I know these are a lot of questions, but with the CLONE environments being located in a different datacenter, that causes some issues. It’s not a problem currently because we restore backups from the SQL AAG secondary servers to the CLONE environments, and the AAG secondary servers are located in the same datacenter as the CLONE servers.

[HannesK]

mounting a clone of the SAN volume in Plano to a local Windows machine in Plano should work, yes. Once the volume is available in Windows, it needs to become part of a Veeam repository. Then a repository rescan is needed an the backups will appear in Backups -> Disk (imported). You can also use a dedicated VBR server only for that. Whatever you prefer.

I would ask Dell on the easiest way to find out about the replication status.

As long as the backup chain is complete (which it must be with volume level replication), the mount to Veeam works.