BE 2012 not backing up SQL 2008 R2

[BackItUpNow]

Hi everyone,
 
I'm fairly new to SQL backups but i understand the fundamentals, though i wanted to double check that what i'm doing is correct or if there's a better way backing up my SLQ server. I want to able to restore SQL databases and or the SQL server to within an hours of failure, data loss or corruption.
 
- I've scheduled a backup job to run like this
 
- Disaster Recovery enabled i.e. All drives + System State and  Microsoft SQL Server Instances are being backup up
 
- Full backup .................................................................................................................................11pm Friday's
- Incremental backup with "Incremental method for Microsoft SQL" selected as Log ..................Every 1 hour
- Incremental backup with "Incremental method for Microsoft SQL" selected as Differential .......Every Day 11pm exc. Friday's

- Use snapshot techonology with "Auto select" snapshot techonology selected
 
 
 
The first full backup works with an exceptions warning (below) but i'll fix that in a different thread.
 
 
 
VSS Snapshot warning. File c:\windows\system32\cba\pds.exe is not present on the snapshot.
 
 
 
Then all differentials and log incremetals fail with V-79-57344-867 - The last Full backup of database Cognos10_Planning was not made by this application. Run a new Full backup, then run this job again.
 
 
I believe i get this error msg because we are also running full database and log backups with the SQL Management Studio, some background info.. i inherited a job where we used to run SQL Full DB (daily) and Log (hourly) backups with Management Studio + Full (daily) Backup Exec jobs without incrementals and worked fine but now i only want Backup Exec to take care of all SQL backup jobs (Full, differentials, logs and so on). I assume by the error msg that i have to delete the mgmt studio backups jobs? Is this best practice?
 
This server is hosted on an ESXi 5.1, with Win 2008 R2 + SQL 2008 R2 SP1 - if there's a better strategy for SQL backups and restores e.g. backup at VM ESXi level and SQL instance please let me know.
 
I want to get this right so your help is much appreciated!
 
Cheers

[pkh · Accepted solution]

ESX 5.1 is currently not supported.  See this blog

https://www-secure.symantec.com/connect/forums/vsphere-51-support-update

The error message will go away when you stop the SQL Studio backup.

You should turn AOF off when you doing a SQL database backup.  As such, you should create 2 backup jobs: one with AOF on for file backups and one with AOF off for SQL backups.

If you have the AVVI agent, then you can backup the entire VM and still be able to restore individual SQL backups.  However, you should not do your VM backups between your full and differential/log backups.  Otherwise, you would get the error message given in your post.

[BackItUpNow]

Thanks for your reply pkh

So does this mean i can just turn off AOF for the below job tasks within the backup job?

- Incremental backup with "Incremental method for Microsoft SQL" selected as Log ..................Every 1 hour
- Incremental backup with "Incremental method for Microsoft SQL" selected as Differential .......Every Day 11pm exc. Friday's

and keep the full backup with AOF turned on? i ask this as i can't create the incrementals in another seperate job without a full backup..

Cheers

[pkh]

No.  AOF should also be off for the full SQL database backup job.  You should not include any file backups in this full job as the file backups would require AOF to be on.  You would need to specify a separate job for your file backups.

[BackItUpNow]

OK, i'm assuming you reccomend the below backup stragtegy...  Isn't this going against "Best practices for Backup Exec 2012 Agent for Microsoft SQL Server" ?
http://www.symantec.com/business/support/index?page=content&id=HOWTO74429

Backup A: Files backup with SDR on and excluding SQL server instance (AOF on)

- Full backup ................................................................................................... 11pm Friday's
- Inc backup with "Inc method for Microsoft SQL" selected as Log ................ Every 1 hour
- Inc backup with "Inc method for Microsoft SQL" selected as Differential ..... Every Day 11pm exc. Friday's

Backup B: SQL server intance selected with (AOF off)

- Full backup ................................................................................................... 11pm Friday's
- Inc backup with "Inc method for Microsoft SQL" selected as Log ................ Every 1 hour
- Inc backup with "Inc method for Microsoft SQL" selected as Differential ..... Every Day 11pm exc. Friday's

The following best practices help you use the SQL Agent effectively:
 •
Back up the entire Microsoft SQL Server.

Include the following in the backup job:

◦
Full SQL database backups

◦
Windows System State

◦
System drive backups of the hard drive or drives where Microsoft SQL resides

◦
System drive backups of the hard drive or drives where the Microsoft SQL databases reside

[pkh]

Your Backup A should not include these

- Inc backup with "Inc method for Microsoft SQL" selected as Log ................ Every 1 hour
- Inc backup with "Inc method for Microsoft SQL" selected as Differential .

The best practice did not specify how many jobs to use.  What it specify is what to back up.

[BackItUpNow]

Sorry that was a copy and paste mistake..

I meant the below

Backup A: Files backup with SDR on and excluding SQL server instance (AOF on)

- Full backup ................................................................................................... 9pm Friday's

Backup B: SQL server intance selected with (AOF off)

- Full backup ................................................................................................... 11pm Friday's
- Inc backup with "Inc method for Microsoft SQL" selected as Log ................ Every 1 hour
- Inc backup with "Inc method for Microsoft SQL" selected as Differential ..... Every Day 11pm exc. Friday's

Do you think a weekly full SDR backup is enough?

Thanks

[pkh]

Do you think a weekly full SDR backup is enough?

It all depends on the requirement of your installation.  Some installation requires a daily full backup.