Comprehensive data protection for all workloads
Post Reply
digriz60
Novice
Posts: 4
Liked: never
Joined: Mar 11, 2019 9:32 pm
Contact:

Suggestions for a SQL server test restore

Post by digriz60 »

Ok, I've been circling around this issue for a couple of weeks. While it would seem straightforward, there is a bit of complexity....I assume...because of some architectual challenges, but mostly because I'm stupid.

We have a critical, production MSSQL 2008 that we hadn't been backing up (the server itself, the databases are backed up with SnapManager, as we run NetApp) because the server itself was so large...1.4 TB. We've honed it down and gotten that shrunk, so I've created a backup job that did a full, and incrementals since then. Bravo to me!

But, the other necessity is doing a test restore and this is where I'm presented with a few options, and a few caveats. We are licnesed for Veeam Backup & Replication Enterprise Standalone. The DB server itself has three "local" drives as part of the VM, and several LUNs, which is where the dbs, snapinfo, logs, etc reside.

It looks like we have the option (for testing purposes) to do an Instant VM restore, a full VM restore, and a SureBackup/Restore. The problem obviously is, because it's a test the real server is still up and running....and I'm trying to do anything to avoid having to bring it down, even for a short maintenance window. If that's the only alternative, then I'll do it, but i wanted to try everything else before having to do that. Why? I'm paranoid and so is my CTO, but moreover, I need this job.

I would think the SureBackup is the safest way to go. I tried the full restore of the VM and while I made the necessary changes to IP and what I thought was the hostname, I clearly didn't know what I was doing, and it wreaked havoc for about 26 minutes. Which is lucky, because policy states you get fired at 27 minutes.

I'm also pretty new with this product, so forgive my ignorance. TIA!
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Suggestions for a SQL server test restore

Post by veremin »

The safest way is SureBackup indeed, as it will spin up VM in completely isolated environment.

You can add your SQL server to application group and enable "Keep the application group running after the job completes" option. This way, after all automatic SureBackup tests are performed, you can log on to running SQL VM and check its validity manually.

Thanks!
digriz60
Novice
Posts: 4
Liked: never
Joined: Mar 11, 2019 9:32 pm
Contact:

Re: Suggestions for a SQL server test restore

Post by digriz60 »

Thank you very much for your input, that helps me direct my energies in the right direction!
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Suggestions for a SQL server test restore

Post by veremin »

You're welcome. Another option might be to create an isolated network, restore VM, using Instant VM Recovery, and connect the VM to the isolated network - sort of manual SureBackup. In this case VM alos won't interfere with production server anyhow.

Thanks!
driley
Influencer
Posts: 22
Liked: 2 times
Joined: Jan 04, 2017 4:49 pm
Full Name: Dennis Riley
Contact:

Re: Suggestions for a SQL server test restore

Post by driley »

digriz60,
Another consideration is what is needed for a valid test. Do you need Active Directory or DNS services, any applications hosted on other machines? If so, you'll need to build that into your isolated network. If you can test by simply logging in to the console with a local machine account and poking around the database, then no need to even connect to a network.
D.
digriz60
Novice
Posts: 4
Liked: never
Joined: Mar 11, 2019 9:32 pm
Contact:

Re: Suggestions for a SQL server test restore

Post by digriz60 »

Practical question: for testing purposes, will I be able to present LUNs to the restored VM, in that, the LUNs are currently live and attached to the live DB server. I think that's my next hurdle, as I assume it would be through NAS or another path since the lab is isolated from the real network. AND I assume you shouldn't present the same LUN to two different servers, even if one is only read only...right?

Obviously, I'm new to this procedure, so I'll ask (or an article link is great) what method should I use to get the Restored VM to see the LUNs. This would only be for testing purposes, I'd say about a half hour to run some queries and confirm the data is accessible.
digriz60
Novice
Posts: 4
Liked: never
Joined: Mar 11, 2019 9:32 pm
Contact:

Re: Suggestions for a SQL server test restore

Post by digriz60 »

Thank you DRiley, that is what I've created so far. I used the wizard to make a virtual lab with one server acting as AD+DNS. That's all we should need in addition to the restored DB Server (plus the NetApp luns I mentioned above. But yes, now that you bring it up, I think that's all we'll need. If this works really well (meaning efficient, clean rollback, etc) I'll be expanding the use of SureBackup, as I see a benefit to ongoing test restores, which we have -as far as I know- been remiss.
Post Reply

Who is online

Users browsing this forum: Google [Bot], Ivan239 and 157 guests