Best practices for backing up SQL in VBR v7

[Dave-Departed]

Hi All,

Right now, we have the DB server of our CRM system, which is still physical, hosting SQL, and being backed up to tape by BackupExec. However, we don't have the SQL module for BE, and so there is a maintenance plan running daily from within SQL, in order to commit/flush the transaction logs.

We plan to P2V this server (I'd much prefer to throw up a clean VM and import the data, but Sage costs for this are extremely restrictive) in the next few months, and I had just assumed that, due to Application-aware processing/ticking the box to flush logs on successful backup, we could just do away with the SQL maintenance plan (because a restored VM via Veeam is to a point in time, and at that point in time, logs will have been flushed, so if we restore to it, then I don't see that there would be a problem?). I've restored Exchange Server VMs in this manner before, as well as our vCenter VM, and all has been fine.

I guess the problem would come if we wanted to restore a SQL DB at the file level, and then import it into a new server (I'm not a DBA, so please forgive my ignorance)? Or if we wanted to restore a DB to its original location, overwriting the existing one? Wouldn't we need logfiles to process there?

I did some reading, and I see a lot of people actually keeping the SQL maintenance plan working, and unticking the flush logs on successful backup box. Is this what we should be doing?

Bearing all of the above in mind, we will only be backing up the DB server once per day (unless it's advised to do it more frequently, and assuming it won't disrupt user experience?).

Many thanks in advance,

Dav

[foggy]

because a restored VM via Veeam is to a point in time, and at that point in time, logs will have been flushed

Dave, currently Veeam B&R does not provide point-in-time SQL restore, that's why:

I see a lot of people actually keeping the SQL maintenance plan working, and unticking the flush logs on successful backup box. Is this what we should be doing?

Here's the adjacent discussion regarding that and there're also a handful of others you can search for for additional information (SQL Backup Jobs, to start with).

[Dave-Departed]

Thanks foggy, I think I understand.

Now, for us... Our current SQL maintenance plan only performs a full backup once per day anyway, and the physical box is then backed up by BackupExec (as I mentioned, no SQL module for BE, so it's effectively the same as unticking the Truncate Logs' box in Veeam). So, if we P2V the server and simply replace both the SQL maintenance plan AND BackupExec job with Veeam, letting it truncate the logs, and we have to restore the entire VM to a point in time, it should be ok, right?

Or we could simply replace the BackupExec job with the Veeam job, unticking Truncate Logs, and let the SQL maintenance plan carry on?

[Vitaliy S.]

Hi Dave,

letting it truncate the logs, and we have to restore the entire VM to a point in time, it should be ok, right?

Yes, if you want to restore just to the moment when VM snapshot was taken, then this approach is fine.

Or we could simply replace the BackupExec job with the Veeam job, unticking Truncate Logs, and let the SQL maintenance plan carry on?

No need to do that, as you're going to backup this VM once a day anyway (similar to what you do with your SQL maintenance plan right now).

Thanks!

[keatho]

Hi,

I've got a few questions about backing up SQL servers and which options to use in the Veeam backup job.
If there is already a post covering this can you please send me a link to that as well.

Here's some info on our setup,
- Currently have two SQL servers (SQL 2005 and SQL 2008R2) which will be upgraded to SQL 2012 later this year
- Both SQL servers have a Maintenance Plan setup through SQL mgmt studio
- Full backup of the DB's once a day
- Transaction logs backed up and truncated once a day for most DB's
- Transaction logs backed up and truncated every 15 mins for two high use DB's

I have a Veeam 6.5 Backup job that runs every night with the following settings,
- Incremental, Synthetic Full, Transform previous full backups
- Enable application-aware image processing
- Truncate logs on successful backup only

With the current settings i can see that the SQL Server Agent and Veeam are both backing up the DB's and Transaction logs as well as truncating the logs.
Will this cause issues with the consistency of the DB's and Log files in the event of a restore ?

Does turning off 'Application-aware image processing' tell the Veeam job not to backup the SQL DB's ?

Should i turn off 'Application-aware image processing' and 'truncate logs' on the Veeam job and just let the SQL Agent manage the DB and log backups, then have Veeam backup the VM each night to capture these native backup files?

By doing this, will i lose the ability to use the Veeam SQL Application Item Recovery Wizard and only be be able to do SQL restores through the SQL mgmt studio?

Thanks very much,
keatho

[veremin]

Will this cause issues with the consistency of the DB's and Log files in the event of a restore ?

If SQL maintenance plan already takes care about log truncation, there is no additional need for VB&R to do the same. Also, this might cause the "consistency" issues, as both solutions aren't aware about each other.

Does turning off 'Application-aware image processing' tell the Veeam job not to backup the SQL DB's ?

The VB&R will perform image-level backup without any application specific steps.

Should i turn off 'Application-aware image processing' and 'truncate logs' on the Veeam job and just let the SQL Agent manage the DB and log backups, then have Veeam backup the VM each night to capture these native backup files?

Yes, you can turn of AAIP completely and let SQL native tools do their work.

By doing this, will i lose the ability to use the Veeam SQL Application Item Recovery Wizard and only be be able to do SQL restores through the SQL mgmt studio?

Even without AAIP SQL AIR wizard you should be able to restore granular SQL data.

Thanks.

[keatho]

Thanks very much for the replies.

[veremin]

You're welcome. Should any other questions arise, feel free to ask. Thanks.

[soylent]

So currently the only way to restore an entire SQL database is to boot the SQL server with SureBackup, log in via RDP and manually run a backup?

[foggy]

Max, not sure I follow you here, could you please clarify? Entire SQL database file can be restored via Windows FLR.

[soylent]

Max, not sure I follow you here, could you please clarify? Entire SQL database file can be restored via Windows FLR.

I was thinking I could use SureBackup to boot up a SQL server backup, then use remote desktop to log into it to get a point-in-time restore using SQL studio. But that means that it would have to already be running a maintenance plan anyway

[foggy]

Procedure that should be used for point-in-time restores is described in this thread.