Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

Scheduling SQL Full Copy Backups

Created: 23 Aug 2012 | 10 comments

I'm sure this has been asked a million times, but I'm not having luck finding the answer. We're looking for one of two ways to schedule GRT backups of our SQL databases without affecting the sequence of backup tasks our DBA has defined in his maintenance plans. Our environment is Backup Exec 2012. The SQL server is virtualized and we have Apps and DB agent installed as well as the AVVI option.

The way I see it, there are two options:

1) Backup the VM using AVVI + GRT. Modify the pre-freeze.bat script to include the -copy flag in each of the calls to BeVSSRequestor.exe.

2) Backup the database(s) directly using the BE agent.

The problem with the AVVI copy option is that it doesn't seem to actually perform a true SQL Full-Copy backup. I can see this specifically if I interrogate the msdb..backupset table and review the LSN and is_copy_only fields for the two requestor records it adds (one for "NULL", which AVVI adds and another for "Backup Exec SQL Agent" which GRT adds). It appears the "NULL" requestor entry is set correctly as copy-only without affecting LSNs, but then the GRT function runs and steps all over the seqencing, so in this sense the "-copy" flag in the pre-freeze script is pretty meaningless...

Conversely, it seems I can only perform copy backups using the SQL agent directly through one-time/unscheduled backups? Really? There's no way to schedule copy backups like there was in BE2010???? Is there any trick around this?

Comments 10 CommentsJump to latest comment

pkh's picture

i am afraid that you would not be able to do a copy backup in BE 2012, like you did in BE 2010.  If you like, you can put this as a suggestion in the Ideas section.  If enough users agree with it, it might be re-implemented in some future release of BE.

BTW, the -copy flag that you are using is so that the VM backup does not interrupt the LSN sequence and is not meant for the copy backup that you want.

Mandar Sahasrabuddhe's picture

At this point of time One Time Backups cannot be scheduled. I'm have forwarded these inputs to our team. A work around could be to invoke the job using BEMCLI command to start the job as per required schedule enforced through Windows Scheduled Tasks functionality.

Regards,
Mandar

 

NESL Netops's picture

Thanks for the replies everyone.

pkh - Thanks for clarifying. The Admin Guide doesn't clearly specify any difference between GRT and non-GRT backups, so I think there was some incorrect conclusions drawn as a result. Honestly, it's pretty useless for GRT backups then. For example, I notice two requestors are added to the msdb..backupset table: one for the AVVI snapshot, the other for the GRT backup. While true, the AVVI is referenced as a copy backup, you are correct that the GRT reference was not. I understand it's probably just how the product was designed, but logically, it doesn't make much sense. (why would I specify an AVVI copy but not want the same when GRT runs?)

Mandar - I don't know if schedule execution using a script that calls a BEMCLI command will work for our scenario. True, it will give us a means to perform a "scheduled copy backup", but a one-time backup doesn't allow for linked duplication jobs which we need for our backup policies.

Taking this functionality out seems like a really uncessary modification to make to the product, especially considering this restriction didn't exist before and I can't for the life of me understand how restricting it creates any benefit.

jc_Independent's picture

Disappointing discovery – Without the “Full Copy-Only” option ability in scheduled jobs - I have abandon using the Backup Exec 2012 SQL agents.

I am in a very similar situation.  All of the SQL systems have dedicated SQL DBA teams with maintenance plans,etc.  BEX is not used as a primary source for "point in time" restores but only for DR, historic, and offsite backups.

Without this feature, there real is no possible way for someone to safety use the product in a large production environment.

James Bourne's picture

It really is quite appaling that the Full Copy Only option has been taken away for scheduled backups in BE 2012.

If this is not resolved then like jc_independent we would have to drop SQL agents.

Not sure what Symantec's thinking is here - why drop functionality!

Roady's picture

We need the Full Copy Only option due to we do weekly and incremental to disk and were doing weekly copy to tape, it makes my boss feel all warm and fuzzy have all the redundancy.  This is going to really make us think about how we do our backup stratgies now.  We NEED the Full Copy Only option put back into BE.

RMJIII's picture

Is there any update from Symantec on this issue? Hard to believe this has not been fixed!

villeah's picture

Does this affect only when taking differential backups from SQL? I am taking daily full SQL backups with BE Agent and weekly full backups with maintenance plans.

jc_Independent's picture

The issue is the biggest impact when you have two applications doing different types of backups. In your example, you have BE and SQL, but both are performing full backups - I don't see you’re going to be impacted. I imagine your databases are in simple and not full recovery mode - and full backups are fine. Be sure to understand the impact of full recovery mode.

KEYPOINT TO UNDERSTAND: When you backup a SQL database, it makes a note of when the database was backed up, the SQL server does, not just your media server. SQL server was designed to be a stand alone systems. It monitors and rememebers backups and alot of other things in its internal system databases.  A Full-Copy is a method to perform a full and not update the internal database that its a full back that can be used during a restore.

Here is the problem that not having full copy function is creating for me, and perhaps will help you better understand running full each day is going to be ok.

The SQL server1 requires point-in-time recovery - ( Server1 is for say a 450 room Resort/Hotel using Agilysys and Accounting system in southern California US - with government monitored development team)

server1 - SQL database full recovery mode / BE Agent / SQL Maintatance plan

backupserver - Backup Exec Core / Daily Full-Copy / Weekly Full-Copy

Before 2012 - No problems / Full recovery ability in disaster, archiving, and user error.

SQL Maintenance plan on server1 is daily full, with hour log/truncate to disk. backupserver - daily full-copy, weekly full-copy.

- When either customer has an issue with data corruption that was discovered by vendor/support. Say accounting puts in bunch of bad info, or perhaps a bad software update runs a weird command against the data. Neither company’s want to go back all the way to the day before. People have paid tickets, and new hotel guests have arrives and departed. Since the databases are in full recovery, the accounting software team, and Agilysys support have front end scripts/menus that they can run, and even through SQL tools to turn all the databases back to say 2 hours via SQL restore commands.

- When the server hardware or site burns down - OR government wants a copy from 2 years ago - I can provide an archived tape. Or spin up a virtual from the backups of the server.

A nice mix of responsibility and work between teams - only possible IF Backup Exec performs a Full-Copy. IN a Full-Copy SQL backup, SQL agrees not to change/note it own internal list of restore point, or change the log truncate points. It’s a Full-Copy.

The problem with not having the Full-Copy is really how SQL records "who" touched the data last and what is needed to perform a point-in-time restore. Here is what happens in a good point in time restore happen, with the above configuration. Simplified of course - no 3 page why I am wrong please, this is just a simple step through to show problem.

1. Request to restore database01 to a day ago to 4:17pm.

2. SQL server checks where the last full back up before the request time is located (Full-Copy is not recorded and isn't listed, thus SQL picks the maintenance plan full)

3. SQL server checks where log/truncated backups files needed. (Full-Copy wasn't listed as a full back up, so all truncate/logs where performed correct, referencing the SQL full backed up correctly.)

4. Restores the full backup, applies the log backups to reaches the 4:17pm request.

In bad configuration / restore where you have mixed say daily full backup with BE and SQL truncate backups, you will get boxed in - either at 2 or 3. Either SQL will request the BE full and want to apply its logs. Or during the BE restore it will not be able to apply the right logs since they referenced/taken by SQL. You end up just going to whoever has the last full closet to the request.

With 2012 not allowing it anymore to be scheduled - you have to pick either disabling all SQL/Vendor or not backing up data at all with agent, and just picking up the files. You will have a more complicated server restore, but you will have data IF the vendor is monitoring its SQL backups.

So... It’s a pretty dirty problem. If you disable the SQL plan & vendor - in an emergency or normal day to day restores - You will not have any support from vendor. They provided a back plan and you disabled it. Backup Exec support will say we restored the data, not thier problem if the application isn't working.  So your customer is out in the cold.

If you just go with vendor backup, you really can’t look anyone in the eye and say you can restore the server. You are just backing up files, and if the application errors, or vendor misconfigures you simply won’t know.

I can come up with reasons why it was removed, none I can really say without appearing judgmental. I have moved away from Backup Exec temporary at company cost. I have high hopes they fix it. I have used/support Backup Exec all the way back to Veritas 10. This is this first time in my life I have every thought of moving to a different application.

To answer your question:  Differential option is a part of the problem, but not just differentials. If you are ok with just restoring back to full, your current configuration you listed seems good.  If you want to expand your knowledge of SQL backups – Do a Google search on difference between Simple & Full recovery mode

villeah's picture

That confirmed my assumptions, thank so much for your great post!