Question in Regards to Restoring a SQL Database

[HJAdams123]

Hello and Good Day Veeam Community...

I am currently evaluating Veeam Backup and Restore 6.1. There is one thing that so far I have not been able to find a workflow for, and that is restoring a SQL database. Not a table in a database, not a SQL query, or a database schema. I want to do something as simple as restore a single database in its entirety from a previous backup. I don't want to target another server, I want to do it in place. How is this accomplished in Veeam B & R 6.1?

Thanks in advance...

-Harold

[foggy]

Harold, you can use File-Level Restore functionality (guest OS files recovery) to restore the database files and copy them to the required location. Thanks!

[HJAdams123]

Yeah, but lets say hypothetically I am trying to restore the database from a previous point in time from one of my backups, how does a file level restore help me? Sure, the file level restore will let me get the *.mdf and *.ldf files from that point in time, but that doesn't help me much in restoring the database. I guess what I am getting at, is what part of Veeam actually hooks into SQL Management Studio and issues the commands to restore the database in place? Are you saying the File Level Restore Wizard is smart enough to know I am picking a SQL database file, and changes some of the screens later in the wizard that hooks into SQL Management studio and gives me a database selection screen to target the restore to?

[foggy]

Well, if you are talking about point-in-time restores, then Veeam B&R does not currently allow that. More details here.

[HJAdams123]

Okay, so how are people recovering their production databases when with Veeam? I find it hard to believe that an enterprise backup solution does not allow you to do point in time restores of your SQL databases.....am I missing something here?

[Cokovic]

For point in time recovery we are still relying on SQL mechanism itself. So we are perfoming transactional backups (and also full backups of the database) to a share and additionally backing up the full VM.

But as far as i know point in time recovery of SQL databases is on the roadmap

[foggy]

Harold, I've added the more detailed link to the previous post, please review. Should answer your questions.

[chadtandy]

Yeah, but lets say hypothetically I am trying to restore the database from a previous point in time from one of my backups, how does a file level restore help me? Sure, the file level restore will let me get the *.mdf and *.ldf files from that point in time, but that doesn't help me much in restoring the database. I guess what I am getting at, is what part of Veeam actually hooks into SQL Management Studio and issues the commands to restore the database in place? Are you saying the File Level Restore Wizard is smart enough to know I am picking a SQL database file, and changes some of the screens later in the wizard that hooks into SQL Management studio and gives me a database selection screen to target the restore to?

What we do is restore the mdf and ldf and then use the import function in SQL to mount the database.

-Chad

[HJAdams123]

Yeah, I guess when I said "point in time", I probably was making people think I wanted Veeam to be able to hook into SQL Management studio and perform all of the work for me. Chad, what you propose would be perfectly fine, just take the mdf and ldf and restore them over top of the existing one. I guess my question there would be, does Veaam "prepare the mdf and ldf file in such a way that that workflow is possible? Is that what the VSS option is for. Say for example, I am protecting a sql server named SQL1 with Veeam. I run a backup that occurs each night at 9pm. I am not doing anything in SQL Managment studio. (only Veaam backups) The next day, my DBA runs a sql script and totally hoses the database. I want to bring just that database back to its condition last night at 9pm. Because all I have is the MDF and LDF of the database inside the backup, I would have to offline the database, and then while its offlined over-write the MDF and LDF with the ones I retrieved using the file level restore wizard from my backup from 9pm....I am assuming that if I had the option of "Application Aware processing" enabled in my backup job, and I had the "truncate log files option" enabled, then the mdf and ldf files can be used in this way for SQL database recovery correct? And I can expect my database will be in the condition it was at 9pm the previous night? (I am not a SQL wizard, so I am just trying to make sure I understand how this would be handled using Veeam)

[tsightler]

Yep, that's exactly it. You use SQL Management Studio and take the database "offline". Once you do that you use Veeam (either IFLR, or the normal FLR GUI) to restore the MDF/LDF files for that database from a Veeam restore point, overwriting the existing files, then simply use SQL Management Studio to put the database back "online". That's all there is to it to restore a SQL database to the point of a Veeam backup restore point. And yes, application aware processing makes sure that this will work 100%.

[HJAdams123]

Awesome, then this would be okay for our needs. Thanks guys, I do appreciate the feedback.

-Harold

[averylarry]

Hey Tom -- it's a computer -- no such thing as 100%

[tsightler]

Actually, I find computers to be about as close to 100% as anything in this world. The people that use them and the environment they're expected to operate in, well that's completely different!

Seriously though, point well taken.

[averylarry]

I'll never forget my computer engineering class where we created some simple circuit using a breadboard. We hooked up an oscilloscope to the circuit. It was basically just a sine wave representing 1s and 0s. Our professor said "watch this". He stood on the other side of the room (15 feet away) and simply flicked his hand in the general direction of the circuit. Sort of like pretending to throw a dart. He caused a noticable spike on the oscilloscope and a data error.

[dellock6]

Ted, that's cheating

Luca.

[tomerl]

Hello All,

we are new for Veeam and so far we are happy with the solution.
One thing that I am not sure how it is done is how to restore entire DB to specific time.
I played with the U-Air restore for SQL, but I don't want Item recovery, just entire DB.

So the question is how do I restore entire DB from point in time to different location instead of overwrite the existing files?

Thanks,
Tomer

[veremin]

Hi, Tomer,

Your post has been merged to the existing discussion regarding SQL point-in-time recovery; so, please, take a look at the answers provided above.

Thanks.

[EHL]

dear all,
how can i restore a backup log in point intime , for exemple , every night i did a full backup at 08PM, and each 30 minutes i did a backup log .
i would restore a full backup , and restore backup log until 11:00 AM , it is possible and how to do with veeam

regards

Eric

[Vitaliy S.]

Hi Eric, in order to use point-in-time restores of the database, you should be using SQL Management Studio, as this operation currently is not available in Veeam B&R natively. See the 1st page of this topic for further reading. Thanks!