transaction log backups are failing

29 Nov 2007 4:35 PM

Sas

All,

I have a SQL Server 2005 STD with SP2 and when i configure the TRA NSACTION
backups through the GUI, I've selected the option to have the transaction log
backed up for "ALL DATABASES" (radio button). Even though there is a note
saying that databases in SIMPLE mode will not be backed up, SQL Server still
tries to back them up and the job fails. I have configure my other SQL Server
2005 with SP1 the same way and it work fine. Could this be a bug?

Cheers!

--
--
"I saw it work in a cartoon once so I am pretty sure I can do it."

29 Nov 2007 4:43 PM

Tibor Karaszi

Maint plans will attempt log backups even for simple databases included in the task. So the solution
is to not attempt log backups for database in simple recovery mode.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"Sas" <S**@discussions.microsoft.com> wrote in message
news:46620E4C-9641-4BDA-A018-62032DF96CFF@microsoft.com...
> All,
>
> I have a SQL Server 2005 STD with SP2 and when i configure the TRA NSACTION
> backups through the GUI, I've selected the option to have the transaction log
> backed up for "ALL DATABASES" (radio button). Even though there is a note
> saying that databases in SIMPLE mode will not be backed up, SQL Server still
> tries to back them up and the job fails. I have configure my other SQL Server
> 2005 with SP1 the same way and it work fine. Could this be a bug?
>
> Cheers!
> --
> --
> "I saw it work in a cartoon once so I am pretty sure I can do it."

29 Nov 2007 5:02 PM

Russell Fields

Therefore, the BOL is telling the truth: "databases in SIMPLE mode will not
be backed up", but it is not telling you that it will record that behavior
as an error. I believe that the non-SIMPLE mode log backups will all
succeed, but your job returns as failed because of the SIMPLE databases. Is
that what you are seeing?

RLF
Show quote

"Sas" <S**@discussions.microsoft.com> wrote in message
news:46620E4C-9641-4BDA-A018-62032DF96CFF@microsoft.com...
> All,
>
> I have a SQL Server 2005 STD with SP2 and when i configure the TRA
> NSACTION
> backups through the GUI, I've selected the option to have the transaction
> log
> backed up for "ALL DATABASES" (radio button). Even though there is a note
> saying that databases in SIMPLE mode will not be backed up, SQL Server
> still
> tries to back them up and the job fails. I have configure my other SQL
> Server
> 2005 with SP1 the same way and it work fine. Could this be a bug?
>
> Cheers!
> --
> --
> "I saw it work in a cartoon once so I am pretty sure I can do it."

29 Nov 2007 6:26 PM

Sas

I just find it strange that I do not get any errors with the server that has
SP1 (and that server does have databases in SIMPLE mode). Anyways what I was
trying to resolve is cases where a new database is created (can be created by
different people without my knowledge). I wanted the new database(s) to be
part of the full backup plan (which is fine) and also part of the transaction
log backup plan if it is in FULL Recovery mode. So I though selecting ALL
DATABASES would do the trick (as it is done with the server that has SP1)...

Thanks for your input guys

Show quote

"Sas" wrote:

> All,
>
> I have a SQL Server 2005 STD with SP2 and when i configure the TRA NSACTION
> backups through the GUI, I've selected the option to have the transaction log
> backed up for "ALL DATABASES" (radio button). Even though there is a note
> saying that databases in SIMPLE mode will not be backed up, SQL Server still
> tries to back them up and the job fails. I have configure my other SQL Server
> 2005 with SP1 the same way and it work fine. Could this be a bug?
>
> Cheers!
> --
> --
> "I saw it work in a cartoon once so I am pretty sure I can do it."