Database restore SQL2005 - Agent breaks SQL log chain

[KeithL-HM]

Hello

I have maint plans set up to do full backups on a weekly basis, daily differential and half hourly transaction log backups. 

I recently tried to restore a database using the SQL Management Console.  I selected a full backup and two of the differentials.   SQL reported that "This differential backup cannot be restored because the database has not been restored to the correct earlier state". 

By checking the SQL log files I have discovered that the Backupexec 12 agent has been creating full backups on a daily basis to virtual devices which explains why I cannot restore the differential.

How can I do a restore to a point in time if these virtual device backups are not available to me???

This leaves the business extremely exposed.  Please help!!!!!!!

Thank you

Keith

[Colin_Weaver]

So are you doing all your backups with Backup Exec or are you doing some of them with the SQL Management Console - as you can't really mix two backup requestors.

[KeithL-HM]

....... we use the BackupExec SQL agent purely to facilitate a speedy disaster recovery.

From a day to day standpoint I run the SQL backups to support business needs.  I wasn't aware (nor was my colleague) that the agent runs a full backup which interferes impacts on the sequence of SQL backups.

Having discussed it with him we're wondering if we could run the agent on a weekly basis (to replace the weekly backups run in SQL) and continue to do the differentials and transaction log backups.

How this would pan out in a full disaster recovery situation I'm not sure at the moment but we need to do some testing.

Thanks for your response anyway !!!

[Ken_Putnam]

"How this would pan out in a full disaster recovery situation "

Shouldn't make any difference.

You do your full restore from BE rather than SQL, then apply your latest DIFF

[pkh]

As Colin said, you should not use two different backup requestors for your SQL backup.  Since you have the BE SQL agent, I would recommend that you use BE exclusively for your SQL Backup.  It is a lot easier to schedule and manage.  It can do anything that the built-in SQL backup facility can do.

[KeithL-HM]

....(at the moment) and we're not sure what it is capable of. 

The daily backups created by the agent are written straight to tape.  Is it possible to force these to disk and is the agent capable of performing differential and transaction log backups please???

Sorry I'm being lazy.  I'm looking for some pointers before investing time in totally rehashing the backups.  I've got 12 servers and nearly 300 databases to manage so I'm more than a bit concerned.

Thanks again for your help!!!!!!!!!!

[pkh]

Is it possible to force these to disk and is the agent capable of performing differential and transaction log backups please???

Yes to both questions.  You can check out the backup options by going to the SQL section of the job properties.  BE uses SQL API to do its work, so it can do what SQL can do in terms of backup and restore.