SQL backup

[jburn]

Hello,

We have a new Exchange 2010 install and wish to test database restores with Veeam before going into production. I know you can restore individual items with uair but we do not have a virtualised domain controller and are not there yet. We wish to be able to restore the database in case of an issue and replay the logs back. Veeam support said to do a file level restore on the database and just copy it over the old database. I expected this not to work and it did not. Veeam support were not able to provide further assistance. Can this be done with Veeam and if so how?

Any advice will be greatly apreciated as we are not exchange experts.

Thanks,

Jamie.

[Vitaliy S.]

Hello Jamie,

Transaction logs replay restores are not possible with Veeam natively, as you've correctly stated. Please take a look at Tom's last reply on the previous page for more info. Thanks!

[Michael_6835]

Follow up question to this:
I am using native SQL to do Full and Transaction log backups of certain databases. I am using Veeam to backup of the entire server nightly.

I have enabled VSS for the SQL job within Veam, I have specified for Veeam to not truncate the logs.

My question is does it matter when the Veeam backup occurs?
Reason for asking is I currently run full SQL backups at 3am and then transactional backups every hour after that.
Do I need to specify Veeam backup to occur sometime prior to the native sql 3am backup? I'm wondering if SQL needs to see the backup that it created via its maintenance policy? or does it not matter?

Does SQL see the backups by veeam vss as regular stand along full backups?
thanks

[Vitaliy S.]

My question is does it matter when the Veeam backup occurs?
Reason for asking is I currently run full SQL backups at 3am and then transactional backups every hour after that.
Do I need to specify Veeam backup to occur sometime prior to the native sql 3am backup? I'm wondering if SQL needs to see the backup that it created via its maintenance policy? or does it not matter?

If you're not truncating logs, then I believe there shouldn't be any difference in scheduling Veeam backup prior or after SQL native backup.

Does SQL see the backups by veeam vss as regular stand along full backups?

SQL Server will be aware that it has been backed up through VSS.

[Michael_6835]

thanks,I realized after signing off that I could schedule my veeam backup at 1am, since it completes fairly quickly.
This would give me a full server image of the prior day up to the backup (1am). I then have my sql backups at 3 and then transactions there after, so I would be good. I am using sql to dump the backups to a network server, then I have backup exec running every few hours to capture those transactions to tape. as well.

[applc]

Hello,

I am looking to do a SQL point-in-time restore, and was wondering what the proper method would be. I use Veeam to do a complete backup of the SQL Server (including databases), and configure the job to NOT truncate the logs. I then use SQL Tools to do a log backup every hour.

My problem is the Veeam restore....which Veeam restore method do I use to restore the database, and leave it in a non-operational mode so I can then restore the logs?

Thanks for any help you can provide.

Regards,
Chris

[Gostev]

Hello Chris, see the last post on the previous page.

[agrob]

Good Day

I've seen that there were similar questions before, but have not found an definitve answer...

We have a vm running with sql server 2008 r2. we have maintenance plans configured:
full dump at 7pm, TRN Backups every 6 hours.
Veeam does Backup the VM with VSS enabled (but transaction log truncating disabled) at 7.30PM

If i look at the sql log when Veeam does backup the system, i can see that there is a virtual device configured (named like {12kjdsiofou....}) on which the backup occure. Backups are consistent, thats fine.

Now if i like to do a point in time restore in sql, sql tells me that i need the full backup (dump) from the veeam backup and trn backups from the maintenance plans. The problem now is does: veeam during the backup process write dump files, and yes to which destination? (the virtual device but where is its destination).

I can extract the mdf, ldf file from the veeam backup, but because these are not sql backup files, i cant do point in time with those files and the trn files from the maintenance plan.

Well we could disable vss for application in veeam, but then in case of a disaster recovery, the sql server would not start i belive.

what is the best way with veeam and coexistence with sql maintenance plans (dump and trn)

Thanks in advance
andre

[lwhitworth]

I've recently been looking into this as have just started using Veeam and hit the same issue. From the reading I've done it seems that you can either do backups out of SQL maintenance plans as you always have done, but you have to disable vss for application in veeam on those servers to do so, or you let Veeam do it and lose the ability to point in time restore. I ended up just sticking to maintenance plans to be honest.

What I believe though is that in the case of disaster recovery there's still a very high chance that your sql servers will start with the DBs intact, as by backing up with Veeam and not using VSS it's now worse than turning off a sql server without shutting down and then expecting it to turn back on, which lets face it works most of the time without incident. If you have a disaster and your SQL server doesn't start you'll still have all your bak and trn files backed up that you can use to restore the DB to as late a point as possible before the disaster.

Hope this helps.

Luke

[agrob]

hi lwhitworth

thanks for your reply... so i think i go an disable application processing and transaction log processing for sql servers and continue to work with maintenance plans. I'll have to test what happens when i try to start the server from one of those backups... if the sql comes up, everythings fine... and as far as i have heard, the only really supported backup way from ms are the maintenance plan...

[rw_ga]

tsightler,

I tested it and it works fine I understand that when I use veeam backup in VSS mode to back up my sql servers, database files should be consistent state when I restore them using Veeam FLR.

If this is the case I can get rid of sql backups altogether.

Thanks for help.

I know this thread hasn't been active lately, but I just ran across it. So if I understand correctly, I can setup my backup jobs as follows and have a very similar point in time restore capability via Veeam?:

Environment:
Veeam B&R v5
SQL 2005 Standard

Backup Destination (third page in job setup):

• Advanced Settings:
  • Backup
    • Backup mode: Incremental (seems to be faster than the reversed one in our environment)
      Active full backup: Weekly on selected days: Everyday
  • vSphere
    • Use changed block tracking data: yes
      • Enable changed block tracking for all processed VMs: yes
  • Advanced
    • Enable VMware tools quiescence: NO
      Safe removal for snapshots larger than: I set this to 10000 MB
      Enable automatic backup integrity checks: yes
      Run the following command: powershell command to launch a Symantec BE incremental job. (move a copy to tape)

Guest Processing (fourth page):

• Enable application-aware image processing: yes
  Enable guest file system indexing: yes

Job Schedule (fifth page):

• Run the job automatically: Periodically every: 20 Minutes, you could even do "Continuously" for near CDP

Then if I had to restore a version of the database (a consistant version ever 20min??? right) I could:

Open Veeam B&R

• Tools
  • File Level Restore
    • Other OS

1. Copy my mdf and log file over to a temp folder on the production database server.
2. Question, do I take the existing bad database offline or do I need to detach it????????
3. Copy the mdf and log file over to where I want it to live when I bring it back online.
4. Then depending on the step above, just bring it back online or attach.

Is this all correct? If so, what step are needed to re-link the good database with the SQL server (2005 by the way)?

Hope this is easy to follow!

Thanks,
Ryan

[Djeten]

Hello,

We're using the Veeam backup & restore for a while now and are continuosly improving our backup scope in the hospital.

Now, within our IT department we're struggling with following question:

We are taking veeam-replicas (& veeam backups in another location) of our SQL 2008 database servers every hour. OK, so far. We are able to run the database from the replica in case of disaster and are able to use the U-AIR SQL recovery for restore of single items within our database.
Next to the above we are taking everyday a full SQL native databasebackup. And have the full recovery model.

When the backup is taken F.ex. at 15:00 and we have a crash at 15:25 our DBA states we have to start the veeam-replica for full recovery (or veeam-backup with instant recovery).
At that time we have the situation of 15:00 online. But we want the situation the minute the crash occured.

Our DBA states we need then commit the full SQL-native-backup of the night in order to be able to commit all the transaction logs to get to the situation of 15:25 (time of the crash).

The full restore is the one that takes a lot of time and widens the restore scope significantly.

Is there any way to start from the veeam replica / veeam backup & the commit the transaction logs to shorten te scope without the full restore?

Thanks for your feedback,


Didier Boydens
IT Manager

[habibalby]

Why don't you doubling the SQL Backup?
Take a SQK Backup on LocalDisk of the VM, then replicate it. If your production server crash and your need the latest restore, you can powerOn your Replica VM and get the Backup of SQL that has been taken on disk, and restore it back to the Production VM.

Or, PowerOn your Relica SQL Server and make sure your DB is up and running and healthy... Then do a Local Backup on disk and transferee the files to your Production environment.

This also it depends on the type of failure you have.. VM Crash, Site Failure, Host Failure, entire Building burns out/collapsed... etc etc.. and also it depends on your RTO..

Thanks,

[Djeten]

Habibalby,

That's what we do now. The only issue is, to be able to do a a restore from the replica, we're first obliged to restore the full SQL-backup in order to be able to commit the SQL-transaction logs. This takes precious time.
While the database from the replica is in fact a lot more recent than the last full SQL-backup. We want to commit the transactionlogs on the replica database to gain time. But that doesn't seem possible.

thanks,

Didier

[habibalby]

Hello Didier..

The bigger the data growth the more time it took to recover.. have a bigger retention period on disk and that's only the option to recover fast as you will run VM from disk only..

[rogerdu]

Is this still the preferred methodology of doing SQL backups?
- setup a job within SQL/Management Studio to backup the DB
- move/copy backup "files" and Transaction Logs to another directory
- backup other directory

and to Restore:
- take the database offline
- Restore backup files and transaction logs
- import backup files into the database

[foggy]

Roger, if you're after point-in-time SQL database recovery, it will be available in the upcoming Veeam B&R v8. Currently point-in-time restores are available via SQL Management Studio only (detailed procedure can be found in this thread above). Thanks!

[KariCo]

The only way to apply transaction log backups is by initially restoring a full backup of the database, leaving the DB in "restoring" state and then apply tlog backup in sequence.
Since you are restoring the .mdf and .ldf files as they were at a certain point in time with VSS Veeam backup, you are not restoring a full backup of the db. You are simply recreating de data and log files of the database, a completely different action.

If you need to apply transaction log backups to recover a database to an specific point in time, or to recovery certain transactions, start by taking a daily full backup of every database with SQL server Mgmt studio or SQL server Maintenance Plan Job.