hello guys,

I have a database on a sql server 2005. A maintenance plan performs a
full backup every night, while a backup of the transaction log is done
every 15 minutes. last night all log backup failed from the early
morning onwards. the error is "BACKUP LOG cannot be performed because
there is no current database backup".

the database is in full recovery mode. any ? I don't quiete know
why this is happening !!

thanks in advance.
zz

Backup the database first.

SQL Server is stopping you from creating a log backup that can not be
used. A log backup can only be used (restored) by first restoring a
full database backup. Only then can log backups (made after that
database backup) be applied, in order and without gaps. The message
is telling you that you don't have a database backup to start the
process.

When starting with a new database in FULL recovery mode if you try to
backup the log before the first backup of the database, that log
backup would be of no use, so it is not allowed. Likewise, if the
recovery model is changed to FULL, log backups are only possible after
a database backup. Only when there has been a database backup, and
the log has been kept intact since that backup, can you backup the
log.

Roy Harvey
Beacon Falls, CT

On Tue, 16 Sep 2008 08:16:24 -0700 (PDT), zerbie45@gmail.com wrote:

>hello guys,
>
>I have a database on a sql server 2005. A maintenance plan performs a
>full backup every night, while a backup of the transaction log is done
>every 15 minutes. last night all log backup failed from the early
>morning onwards. the error is "BACKUP LOG cannot be performed because
>there is no current database backup".
>
>the database is in full recovery mode. any ? I don't quiete know
>why this is happening !!
>
>thanks in advance.
>zz

Another case when this happens is if somebody does BACKUP LOG WITH TRUNCATE_ONLY (or NO_LOG) which
also break the log backup sequence causing this error until a full db backup has been performed.

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


"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:55kvc49omv7q4ukke0p1dg1toaaf8rjpmi@4ax.com...
> Backup the database first.
>
> SQL Server is stopping you from creating a log backup that can not be
> used. A log backup can only be used (restored) by first restoring a
> full database backup. Only then can log backups (made after that
> database backup) be applied, in order and without gaps. The message
> is telling you that you don't have a database backup to start the
> process.
>
> When starting with a new database in FULL recovery mode if you try to
> backup the log before the first backup of the database, that log
> backup would be of no use, so it is not allowed. Likewise, if the
> recovery model is changed to FULL, log backups are only possible after
> a database backup. Only when there has been a database backup, and
> the log has been kept intact since that backup, can you backup the
> log.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 16 Sep 2008 08:16:24 -0700 (PDT), zerbie45@gmail.com wrote:
>
>>hello guys,
>>
>>I have a database on a sql server 2005. A maintenance plan performs a
>>full backup every night, while a backup of the transaction log is done
>>every 15 minutes. last night all log backup failed from the early
>>morning onwards. the error is "BACKUP LOG cannot be performed because
>>there is no current database backup".
>>
>>the database is in full recovery mode. any ? I don't quiete know
>>why this is happening !!
>>
>>thanks in advance.
>>zz

Or if someone switches to SIMPLE mode and then back to FULL.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:eqf8I9CGJHA.2076@TK2MSFTNGP04.phx.gbl...
> Another case when this happens is if somebody does BACKUP LOG WITH
> TRUNCATE_ONLY (or NO_LOG) which also break the log backup sequence causing
> this error until a full db backup has been performed.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
> news:55kvc49omv7q4ukke0p1dg1toaaf8rjpmi@4ax.com...
>> Backup the database first.
>>
>> SQL Server is stopping you from creating a log backup that can not be
>> used. A log backup can only be used (restored) by first restoring a
>> full database backup. Only then can log backups (made after that
>> database backup) be applied, in order and without gaps. The message
>> is telling you that you don't have a database backup to start the
>> process.
>>
>> When starting with a new database in FULL recovery mode if you try to
>> backup the log before the first backup of the database, that log
>> backup would be of no use, so it is not allowed. Likewise, if the
>> recovery model is changed to FULL, log backups are only possible after
>> a database backup. Only when there has been a database backup, and
>> the log has been kept intact since that backup, can you backup the
>> log.
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Tue, 16 Sep 2008 08:16:24 -0700 (PDT), zerbie45@gmail.com wrote:
>>
>>>hello guys,
>>>
>>>I have a database on a sql server 2005. A maintenance plan performs a
>>>full backup every night, while a backup of the transaction log is done
>>>every 15 minutes. last night all log backup failed from the early
>>>morning onwards. the error is "BACKUP LOG cannot be performed because
>>>there is no current database backup".
>>>
>>>the database is in full recovery mode. any ? I don't quiete know
>>>why this is happening !!
>>>
>>>thanks in advance.
>>>zz
>

On Tue, 16 Sep 2008 12:30:47 -0700, "Kalen Delaney"
<replies@public_newsgroups.com> wrote:

>Or if someone switches to SIMPLE mode and then back to FULL.

Which I (sort of) covered with the line " Likewise, if the recovery
model is changed to FULL..."

Roy Harvey
Beacon Falls, CT

Yes, you did. Sorry for butting in...
;-)

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:sp20d4th8c6afo0v1gqlc6f54hsbrg2d5d@4ax.com...
> On Tue, 16 Sep 2008 12:30:47 -0700, "Kalen Delaney"
> <replies@public_newsgroups.com> wrote:
>
>>Or if someone switches to SIMPLE mode and then back to FULL.
>
> Which I (sort of) covered with the line " Likewise, if the recovery
> model is changed to FULL..."
>
> Roy Harvey
> Beacon Falls, CT

On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
<replies@public_newsgroups.com> wrote:

>Yes, you did. Sorry for butting in...
>;-)

You were not butting in, just attempting to clarify my murky
explanation. 8-)

Roy Harvey
Beacon Falls, CT

On Sep 16, 10:37pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
>
> <replies@public_newsgroups.com> wrote:
> >Yes, you did. Sorry for butting in...
> >;-)
>
> You were not butting in, just attempting to clarify my murky
> explanation. 8-)
>
> Roy Harvey
> Beacon Falls, CT

thanks for all the answers. I still don't understand what
happened.recovery mode is still full, and as soon as I performed a new
full backup, transaction log backup were performing fine again.
the strange thing is a full backup had been done just an hour before,
than suddenly the transaction log backups stopped working.
now everything is back to normal, but I really don't understand why
this happened and I'm afraid it might happen again.

Just to make it clear, the error was "BACKUP LOG cannot be performed
because there is no current database backup" which I don't quiete
understand. I do a daily full backup and transaction log backup every
15 minutes, nothing fancy, no other options, just old fashioned plain
backups using the maintenance plan gui. I'll see tomorrow morning how
it goes...

Any guidance is appreciated.
Thanks!

The error message is a bit of an oversimplification. As Roy explained, not
having a full backup is ONE of the reasons you might get this message. We
listed the other reasons in this thread.
We can't tell what happened to cause a break in the backup chain and prevent
further log backups. You'll have to check all the scripts and jobs that do
log backups and make sure none of them use TRUNCATE_ONLY or NO_LOG and make
sure no one switches the db to SIMPLE.

If you are in FULL mode and make a normal database backup, someone has to DO
something 'special' for this error to be generated. It won't just happen on
its own.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com


<zerbie45@gmail.com> wrote in message
news:14645beb-00d5-4521-9166-c3d3c5af218e@b1g2000hsg.googlegroups.com...
On Sep 16, 10:37 pm, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
>
> <replies@public_newsgroups.com> wrote:
> >Yes, you did. Sorry for butting in...
> >;-)
>
> You were not butting in, just attempting to clarify my murky
> explanation. 8-)
>
> Roy Harvey
> Beacon Falls, CT

thanks for all the answers. I still don't understand what
happened.recovery mode is still full, and as soon as I performed a new
full backup, transaction log backup were performing fine again.
the strange thing is a full backup had been done just an hour before,
than suddenly the transaction log backups stopped working.
now everything is back to normal, but I really don't understand why
this happened and I'm afraid it might happen again.

Just to make it clear, the error was "BACKUP LOG cannot be performed
because there is no current database backup" which I don't quiete
understand. I do a daily full backup and transaction log backup every
15 minutes, nothing fancy, no other options, just old fashioned plain
backups using the maintenance plan gui. I'll see tomorrow morning how
it goes...

Any guidance is appreciated.
Thanks!

In my experience it is surprisingly common to find various types of jobs that includes BACKUP LOG
.... WITH TRUNCATE_ONLY in them. And frequently these were added by developers, not db admins.
Perhaps this is one for the reasons why this command no longer work in 2008 - so that the db admin
regain control over the backup situation...

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


"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:OW%23MYSFGJHA.2292@TK2MSFTNGP04.phx.gbl...
> The error message is a bit of an oversimplification. As Roy explained, not having a full backup is
> ONE of the reasons you might get this message. We listed the other reasons in this thread.
> We can't tell what happened to cause a break in the backup chain and prevent further log backups.
> You'll have to check all the scripts and jobs that do log backups and make sure none of them use
> TRUNCATE_ONLY or NO_LOG and make sure no one switches the db to SIMPLE.
>
> If you are in FULL mode and make a normal database backup, someone has to DO something 'special'
> for this error to be generated. It won't just happen on its own.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> www.SQLTuners.com
>
>
> <zerbie45@gmail.com> wrote in message
> news:14645beb-00d5-4521-9166-c3d3c5af218e@b1g2000hsg.googlegroups.com...
> On Sep 16, 10:37 pm, "Roy Harvey (SQL Server MVP)"
> <roy_har...@snet.net> wrote:
>> On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
>>
>> <replies@public_newsgroups.com> wrote:
>> >Yes, you did. Sorry for butting in...
>> >;-)
>>
>> You were not butting in, just attempting to clarify my murky
>> explanation. 8-)
>>
>> Roy Harvey
>> Beacon Falls, CT
>
> thanks for all the answers. I still don't understand what
> happened.recovery mode is still full, and as soon as I performed a new
> full backup, transaction log backup were performing fine again.
> the strange thing is a full backup had been done just an hour before,
> than suddenly the transaction log backups stopped working.
> now everything is back to normal, but I really don't understand why
> this happened and I'm afraid it might happen again.
>
> Just to make it clear, the error was "BACKUP LOG cannot be performed
> because there is no current database backup" which I don't quiete
> understand. I do a daily full backup and transaction log backup every
> 15 minutes, nothing fancy, no other options, just old fashioned plain
> backups using the maintenance plan gui. I'll see tomorrow morning how
> it goes...
>
> Any guidance is appreciated.
> Thanks!
>
>
>
>
>
>

On Sep 17, 9:58am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> In my experience it is surprisingly common to find various types of jobs that includes BACKUP LOG
> ... WITH TRUNCATE_ONLY in them. And frequently these were added by developers, not db admins.
> Perhaps this is one for the reasons why this command no longer work in 2008 - so that the db admin
> regain control over the backup situation...
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
>
> news:OW%23MYSFGJHA.2292@TK2MSFTNGP04.phx.gbl...
>
>
>
> > The error message is a bit of an oversimplification. As Roy explained, not having a full backup is
> > ONE of the reasons you might get this message. We listed the other reasons in this thread.
> > We can't tell what happened to cause a break in the backup chain and prevent further log backups.
> > You'll have to check all the scripts and jobs that do log backups and make sure none of them use
> > TRUNCATE_ONLY or NO_LOG and make sure no one switches the db to SIMPLE.
>
> > If you are in FULL mode and make a normal database backup, someone has to DO something 'special'
> > for this error to be generated. It won't just happen on its own.
>
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >www.InsideSQLServer.com
> >www.SQLTuners.com
>
> > <zerbi...@gmail.com> wrote in message
> >news:14645beb-00d5-4521-9166-c3d3c5af218e@b1g2000hsg.googlegroups.com...
> > On Sep 16, 10:37 pm, "Roy Harvey (SQL Server MVP)"
> > <roy_har...@snet.net> wrote:
> >> On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
>
> >> <replies@public_newsgroups.com> wrote:
> >> >Yes, you did. Sorry for butting in...
> >> >;-)
>
> >> You were not butting in, just attempting to clarify my murky
> >> explanation. 8-)
>
> >> Roy Harvey
> >> Beacon Falls, CT
>
> > thanks for all the answers. I still don't understand what
> > happened.recovery mode is still full, and as soon as I performed a new
> > full backup, transaction log backup were performing fine again.
> > the strange thing is a full backup had been done just an hour before,
> > than suddenly the transaction log backups stopped working.
> > now everything is back to normal, but I really don't understand why
> > this happened and I'm afraid it might happen again.
>
> > Just to make it clear, the error was "BACKUP LOG cannot be performed
> > because there is no current database backup" which I don't quiete
> > understand. I do a daily full backup and transaction log backup every
> > 15 minutes, nothing fancy, no other options, just old fashioned plain
> > backups using the maintenance plan gui. I'll see tomorrow morning how
> > it goes...
>
> > Any guidance is appreciated.
> > Thanks!- Hide quoted text -
>
> - Show quoted text -

hello guys, sorry but id did happen again!
I checked and I did not use truncate_only in my backups (they are
built using the ssms gui and I don't even see the option for doing
this...?).
the full backup was performed last night at midnight (172 MB), then I
see a t log backup at 00:15 (262 MB), then another t log backup at
00:30 (105 KB), another one at 00:45 (40 KB), and the last one at
01:00 (40 KB), then nothing... weird!!! of course there's plenty of
disk space...

this is part of the error log I get:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "dbserver01".
Maintenance Plan: Backup IM6 Tlog
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database Task (dbserver01)
Backup Database on Local server connection
Databases: mydatabase
Type: Transaction Log
Append existing
Task start: 2008-09-17T09:30:11.
Task end: 2008-09-17T09:30:11.
Failed-1073548784) Executing the query "BACKUP LOG [mydatabase] TO
DISK = N'E:\\Backup\\SQL\\mydatabase\
\mydatabase_backup_200809170930.trn' WITH NOFORMAT, NOINIT, NAME =
N'mydatabase_backup_20080917093011', SKIP, REWIND, NOUNLOAD, STATS =
10
" failed with the following error: "BACKUP LOG cannot be performed
because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [mydatabase] TO DISK = N''E:\Backup\SQL\mydatabase
\mydatabase_backup_200809170930.trn'' WITH NOFORMAT, NOINIT, NAME =
N''mydatabase_backup_20080917093011'', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''mydatabase'' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N''mydatabase'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup
information for database ''''mydatabase'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''E:\Backup\SQL\mydatabase
\mydatabase_backup_200809170930.trn'' WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND


Back Up Database Task (dbserver01)
Backup Database on Local server connection
Databases: mydatabase
Type: Transaction Log
Append existing
Task start: 2008-09-17T09:30:11.
Task end: 2008-09-17T09:30:11.
Failed-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N'mydatabase' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N'mydatabase' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup
information for database ''mydatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\\Backup\\SQL\\mydatabase\
\mydatabase_backup_200809170930.trn' WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device 'E:\
\Backup\\SQL\\mydatabase\\mydatabase_backup_200809 170930.trn'.
Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

any , again, is much appreciated.

You need to track down whoever is doing this. One option is to have a profiler trace running and
capture commands like BACKUP, ALTER DATABASE and sp_dboption.

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


<zerbie45@gmail.com> wrote in message
news:0a2e93dd-7183-4cbd-88eb-d220c9f9c3f6@34g2000hsh.googlegroups.com...
On Sep 17, 9:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> In my experience it is surprisingly common to find various types of jobs that includes BACKUP LOG
> ... WITH TRUNCATE_ONLY in them. And frequently these were added by developers, not db admins.
> Perhaps this is one for the reasons why this command no longer work in 2008 - so that the db admin
> regain control over the backup situation...
>
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
>
> news:OW%23MYSFGJHA.2292@TK2MSFTNGP04.phx.gbl...
>
>
>
> > The error message is a bit of an oversimplification. As Roy explained, not having a full backup
> > is
> > ONE of the reasons you might get this message. We listed the other reasons in this thread.
> > We can't tell what happened to cause a break in the backup chain and prevent further log
> > backups.
> > You'll have to check all the scripts and jobs that do log backups and make sure none of them use
> > TRUNCATE_ONLY or NO_LOG and make sure no one switches the db to SIMPLE.
>
> > If you are in FULL mode and make a normal database backup, someone has to DO something 'special'
> > for this error to be generated. It won't just happen on its own.
>
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >www.InsideSQLServer.com
> >www.SQLTuners.com
>
> > <zerbi...@gmail.com> wrote in message
> >news:14645beb-00d5-4521-9166-c3d3c5af218e@b1g2000hsg.googlegroups.com...
> > On Sep 16, 10:37 pm, "Roy Harvey (SQL Server MVP)"
> > <roy_har...@snet.net> wrote:
> >> On Tue, 16 Sep 2008 12:54:31 -0700, "Kalen Delaney"
>
> >> <replies@public_newsgroups.com> wrote:
> >> >Yes, you did. Sorry for butting in...
> >> >;-)
>
> >> You were not butting in, just attempting to clarify my murky
> >> explanation. 8-)
>
> >> Roy Harvey
> >> Beacon Falls, CT
>
> > thanks for all the answers. I still don't understand what
> > happened.recovery mode is still full, and as soon as I performed a new
> > full backup, transaction log backup were performing fine again.
> > the strange thing is a full backup had been done just an hour before,
> > than suddenly the transaction log backups stopped working.
> > now everything is back to normal, but I really don't understand why
> > this happened and I'm afraid it might happen again.
>
> > Just to make it clear, the error was "BACKUP LOG cannot be performed
> > because there is no current database backup" which I don't quiete
> > understand. I do a daily full backup and transaction log backup every
> > 15 minutes, nothing fancy, no other options, just old fashioned plain
> > backups using the maintenance plan gui. I'll see tomorrow morning how
> > it goes...
>
> > Any guidance is appreciated.
> > Thanks!- Hide quoted text -
>
> - Show quoted text -

hello guys, sorry but id did happen again!
I checked and I did not use truncate_only in my backups (they are
built using the ssms gui and I don't even see the option for doing
this...?).
the full backup was performed last night at midnight (172 MB), then I
see a t log backup at 00:15 (262 MB), then another t log backup at
00:30 (105 KB), another one at 00:45 (40 KB), and the last one at
01:00 (40 KB), then nothing... weird!!! of course there's plenty of
disk space...

this is part of the error log I get:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "dbserver01".
Maintenance Plan: Backup IM6 Tlog
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database Task (dbserver01)
Backup Database on Local server connection
Databases: mydatabase
Type: Transaction Log
Append existing
Task start: 2008-09-17T09:30:11.
Task end: 2008-09-17T09:30:11.
Failed-1073548784) Executing the query "BACKUP LOG [mydatabase] TO
DISK = N'E:\\Backup\\SQL\\mydatabase\
\mydatabase_backup_200809170930.trn' WITH NOFORMAT, NOINIT, NAME =
N'mydatabase_backup_20080917093011', SKIP, REWIND, NOUNLOAD, STATS =
10
" failed with the following error: "BACKUP LOG cannot be performed
because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [mydatabase] TO DISK = N''E:\Backup\SQL\mydatabase
\mydatabase_backup_200809170930.trn'' WITH NOFORMAT, NOINIT, NAME =
N''mydatabase_backup_20080917093011'', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''mydatabase'' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N''mydatabase'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup
information for database ''''mydatabase'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''E:\Backup\SQL\mydatabase
\mydatabase_backup_200809170930.trn'' WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND


Back Up Database Task (dbserver01)
Backup Database on Local server connection
Databases: mydatabase
Type: Transaction Log
Append existing
Task start: 2008-09-17T09:30:11.
Task end: 2008-09-17T09:30:11.
Failed-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N'mydatabase' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N'mydatabase' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup
information for database ''mydatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\\Backup\\SQL\\mydatabase\
\mydatabase_backup_200809170930.trn' WITH FILE = @backupSetId,
NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device 'E:\
\Backup\\SQL\\mydatabase\\mydatabase_backup_200809 170930.trn'.
Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

any , again, is much appreciated.

hi tibor,

what should I try to track down ? doing what ?
are you able to spot anything unusual in the log file ?

thanks for your !

On Sep 17, 11:02am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> You need to track down whoever is doing this. One option is to have a profiler trace running and
> capture commands like BACKUP, ALTER DATABASE and sp_dboption.
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>

On Wed, 17 Sep 2008 02:15:54 -0700 (PDT), zerbie45@gmail.com wrote:

>what should I try to track down ? doing what ?
>are you able to spot anything unusual in the log file ?

Sometime after the last successful log backup, and before the first
log backup failure, some person or task screwed with the logs. The
error message you posted was after the damage was done, it only shows
there is a problem. You need to find out what happened before that,
and a profiler trace is one way.

Roy Harvey
Beacon Falls, CT

On Sep 17, 11:38am, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> On Wed, 17 Sep 2008 02:15:54 -0700 (PDT), zerbi...@gmail.com wrote:
> >what should I try to track down ? doing what ?
> >are you able to spot anything unusual in the log file ?
>
> Sometime after the last successful log backup, and before the first
> log backup failure, some person or task screwed with the logs. The
> error message you posted was after the damage was done, it only shows
> there is a problem. You need to find out what happened before that,
> and a profiler trace is one way.
>
> Roy Harvey
> Beacon Falls, CT

hello roy, well nobody is messing with this. the database is on a non
production server.
but I did find something.

between the last successfull trans log backup and the first
unsuccessfull log backup, there is a scheduled procedure that cleans
up the database. this application writes this information in the event
log "Newday(mydatabase) - Clearing the DB Transactionlog."
right afterwards, sql writes this information:
"BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The
simple recovery model should be used to automatically truncate the
transaction log."

is it this scheduled application (1 AM everyday) that messes up with
my backup schedules and invalidates something ?

Thanks again!

> is it this scheduled application (1 AM everyday) that messes up with
> my backup schedules and invalidates something ?

Yes it is. It does exactly what we suspected that somebody is doing.

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


<zerbie45@gmail.com> wrote in message
news:7bbf3d1c-5d1e-4fe5-b8ff-bc46c0c9d874@25g2000hsx.googlegroups.com...
On Sep 17, 11:38 am, "Roy Harvey (SQL Server MVP)"
<roy_har...@snet.net> wrote:
> On Wed, 17 Sep 2008 02:15:54 -0700 (PDT), zerbi...@gmail.com wrote:
> >what should I try to track down ? doing what ?
> >are you able to spot anything unusual in the log file ?
>
> Sometime after the last successful log backup, and before the first
> log backup failure, some person or task screwed with the logs. The
> error message you posted was after the damage was done, it only shows
> there is a problem. You need to find out what happened before that,
> and a profiler trace is one way.
>
> Roy Harvey
> Beacon Falls, CT

hello roy, well nobody is messing with this. the database is on a non
production server.
but I did find something.

between the last successfull trans log backup and the first
unsuccessfull log backup, there is a scheduled procedure that cleans
up the database. this application writes this information in the event
log "Newday(mydatabase) - Clearing the DB Transactionlog."
right afterwards, sql writes this information:
"BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The
simple recovery model should be used to automatically truncate the
transaction log."

is it this scheduled application (1 AM everyday) that messes up with
my backup schedules and invalidates something ?

Thanks again!

Zerbie45,

I'm getting exactly the same symptoms as you - same messages in the logs.
Haven't tracked it down yet, though.

On Sep 17, 4:18pm, Profanity'smymiddlename
<Profanity'smymiddlen...@discussions.microsoft.com > wrote:
> Zerbie45,
>
> I'm getting exactly the same symptoms as you - same messages in the logs.
> Haven't tracked it down yet, though.

hello,

thanks to all who answered to me with this issue.
well I found out what the problem was: that application that runs at
midnight, just after the full database backup, puts temporarily the
database in simple mode. of course this invalidates the backup and
therefore transaction log backups fail. Now I just rescheduled the db
backup right after this procedure so that the problem should be
solved.

again, thanks to all of those who answered.

have a nice day,
zz

<zerbie45@gmail.com> wrote in message
news:3855b07a-c755-413c-8b1e-a49408c636f6@f63g2000hsf.googlegroups.com...
On Sep 17, 4:18 pm, Profanity'smymiddlename
<Profanity'smymiddlen...@discussions.microsoft.com > wrote:
> Zerbie45,
>
> I'm getting exactly the same symptoms as you - same messages in the logs.
> Haven't tracked it down yet, though.

hello,

thanks to all who answered to me with this issue.
well I found out what the problem was: that application that runs at
midnight, just after the full database backup, puts temporarily the
database in simple mode. of course this invalidates the backup and
therefore transaction log backups fail. Now I just rescheduled the db
backup right after this procedure so that the problem should be
solved.

again, thanks to all of those who answered.

have a nice day,
zz

<<zerbie45@gmail.com> wrote in message
news:3855b07a-c755-413c-8b1e-a49408c636f6@f63g2000hsf.googlegroups.com...
<On Sep 17, 4:18 pm, Profanity'smymiddlename
<<Profanity'smymiddlen...@discussions.microsoft.co m> wrote:
<> Zerbie45,
<>
<> I'm getting exactly the same symptoms as you - same messages in the logs.
<> Haven't tracked it down yet, though.
<
<hello,
<
<thanks to all who answered to me with this issue.
<well I found out what the problem was: that application that runs at
<midnight, just after the full database backup, puts temporarily the
<database in simple mode. of course this invalidates the backup and
<therefore transaction log backups fail. Now I just rescheduled the db
<backup right after this procedure so that the problem should be
<solved.
<
<again, thanks to all of those who answered.
<
<have a nice day,
<zz

Sorry about the blank message, hit send by mistake.

If you wrote this application in house, I would almost certainly want change
it so it didn't put the database in simple recovery mode. If it's from a
vendor, I would ask them to change it. It is almost always bad practice to
routinely place a database that is in full recovery mode to simple and then
back to full. Doing that breaks the backup chain every night. To see why
that is a bad idea, consider the following scenerio.

Your database fails at 4PM today. So you want to backup the tail of the
log, then restore the full backup from midnight and then all the logs. But
if the full backup is not readable, you would normally just go to the full
backup from the previouse night and then apply all the logs. But you can't
do that because the log chain breaks every night. Unless this scenerio is
not a problem to you (going to your boss and saying, "Sorry, the best we can
do is restore the database to about midnight and we lost all the work from
today", you don't want to break the log chain.

Tom

On Sep 17, 11:20pm, "Tom Cooper"
<tomcoo...@comcast.no.spam.please.net> wrote:
> <<zerbi...@gmail.com> wrote in message
>
> news:3855b07a-c755-413c-8b1e-a49408c636f6@f63g2000hsf.googlegroups.com...
> <On Sep 17, 4:18 pm, Profanity'smymiddlename<<Profanity'smymiddlen...@d iscussions.microsoft.com> wrote:
>
> <> Zerbie45,
> <>
> <> I'm getting exactly the same symptoms as you - same messages in the logs.
> <> Haven't tracked it down yet, though.
> <
> <hello,
> <
> <thanks to all who answered to me with this issue.
> <well I found out what the problem was: that application that runs at
> <midnight, just after the full database backup, puts temporarily the
> <database in simple mode. of course this invalidates the backup and
> <therefore transaction log backups fail. Now I just rescheduled the db
> <backup right after this procedure so that the problem should be
> <solved.
> <
> <again, thanks to all of those who answered.
> <
> <have a nice day,
> <zz
>
> Sorry about the blank message, hit send by mistake.
>
> If you wrote this application in house, I would almost certainly want change
> it so it didn't put the database in simple recovery mode. If it's froma
> vendor, I would ask them to change it. It is almost always bad practice to
> routinely place a database that is in full recovery mode to simple and then
> back to full. Doing that breaks the backup chain every night. To see why
> that is a bad idea, consider the following scenerio.
>
> Your database fails at 4PM today. So you want to backup the tail of the
> log, then restore the full backup from midnight and then all the logs. But
> if the full backup is not readable, you would normally just go to the full
> backup from the previouse night and then apply all the logs. But you can't
> do that because the log chain breaks every night. Unless this sceneriois
> not a problem to you (going to your boss and saying, "Sorry, the best we can
> do is restore the database to about midnight and we lost all the work from
> today", you don't want to break the log chain.
>
> Tom

hi tom,

that's a very good point, shame on me I didn't think about it! the
application is not written in-house. it's a third-party component of a
financial software suite for the banking industry. I will write to
them tomorrow morning!

thanks!
zz