Comprehensive data protection for all workloads
Post Reply
HJAdams123
Enthusiast
Posts: 60
Liked: 15 times
Joined: Jul 16, 2012 1:54 pm
Full Name: Harold Adams
Contact:

Question in Regards to Restoring a SQL Database

Post by HJAdams123 » 1 person likes this post

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
Veeam Software
Posts: 19542
Liked: 1781 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Enthusiast
Posts: 60
Liked: 15 times
Joined: Jul 16, 2012 1:54 pm
Full Name: Harold Adams
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Veeam Software
Posts: 19542
Liked: 1781 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by foggy »

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

HJAdams123
Enthusiast
Posts: 60
Liked: 15 times
Joined: Jul 16, 2012 1:54 pm
Full Name: Harold Adams
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Expert
Posts: 295
Liked: 59 times
Joined: Sep 06, 2011 8:45 am
Full Name: Haris Cokovic
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Veeam Software
Posts: 19542
Liked: 1781 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by foggy »

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

chadtandy
Enthusiast
Posts: 36
Liked: never
Joined: Feb 09, 2010 8:26 pm
Full Name: Chad
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by chadtandy »

HJAdams123 wrote: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
Enthusiast
Posts: 60
Liked: 15 times
Joined: Jul 16, 2012 1:54 pm
Full Name: Harold Adams
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by HJAdams123 » 1 person likes this post

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
VP, Product Management
Posts: 5688
Liked: 2509 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by tsightler » 2 people like this post

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
Enthusiast
Posts: 60
Liked: 15 times
Joined: Jul 16, 2012 1:54 pm
Full Name: Harold Adams
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by HJAdams123 »

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

-Harold

averylarry
Expert
Posts: 264
Liked: 28 times
Joined: Mar 22, 2011 7:43 pm
Full Name: Ted
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by averylarry »

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

tsightler
VP, Product Management
Posts: 5688
Liked: 2509 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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! :mrgreen:

Seriously though, point well taken.

averylarry
Expert
Posts: 264
Liked: 28 times
Joined: Mar 22, 2011 7:43 pm
Full Name: Ted
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Veeam Software
Posts: 5940
Liked: 1763 times
Joined: Jul 26, 2009 3:39 pm
Full Name: Luca Dell'Oca
Location: Varese, Italy
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by dellock6 »

Ted, that's cheating :P

Luca.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software

@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2020
Veeam VMCE #1

tomerl
Novice
Posts: 9
Liked: never
Joined: May 08, 2013 7:00 am
Full Name: Tomer Leibovich
Contact:

[MERGED] Point-in-time restore for MSSQL

Post by 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
Product Manager
Posts: 17900
Liked: 1673 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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
Lurker
Posts: 1
Liked: never
Joined: May 01, 2014 7:52 am
Full Name: EHL
Contact:

[MERGED] SQL Server restoring backup log

Post by 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.
Product Manager
Posts: 24345
Liked: 1891 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: Question in Regards to Restoring a SQL Database

Post by 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!

Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 60 guests