Backup Database Failure

I have two databases that both give the following error when attempting to
do a BACKUP LOG:
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
The backup statement is this:
backup log [00000005]
to disk='e:\MSSQL Backup\20071227\00000005_logbackup_20071
2271121.TRN'
with name = 'e:\MSSQL Backup\20071227\00000005_logbackup_20071
2271121.TRN',
description = 'Nightly Maintenance', password='thepassword'
I have verified the following: There are multiple full backups for both,
including one last night around 7pm. I did those manually. I also did 5
transaction log backups in a row for each with no problem, 4 manually the
final using a scheduled job. If I use SSMS and go to Tasks - Restore
Database, the Full backup and all 5 Log Backups do populate in the grid of
available backups to restore from, so the system is definitely seeing them.
In msdb.dbo.backupset the only difference I note is that the username for
the Full and first 4 tlog backups is SA and the last tlog backup is a domain
admin account used by SQL Agent. I did try the above backup both using SA
and the SQL Agent job. Both give same error. I have also verified that
these two databases have not been restored since the last log backup.
I checked profiler but it blanks-out all BACKUP operations for security
reasons.
Any ideas'
Server is 2005, build 1366. Looks like I need to upgrade to latest service
pack at some point, although I did review the fix lists for SP1 and 2 and
didn't see anything that would cause this.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netCheck msdb..restorehistory to see if something is doing a restore operation
that you are not aware of...I've seen this, but don;t recall the conditions
or product
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n7m6plje6dv17@.corp.supernews.com...
>I have two databases that both give the following error when attempting to
>do a BACKUP LOG:
> Msg 4214, Level 16, State 1, Line 1
> BACKUP LOG cannot be performed because there is no current database
> backup.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> The backup statement is this:
> backup log [00000005]
> to disk='e:\MSSQL Backup\20071227\00000005_logbackup_20071
2271121.TRN'
> with name = 'e:\MSSQL
> Backup\20071227\00000005_logbackup_20071
2271121.TRN', description =
> 'Nightly Maintenance', password='thepassword'
>
> I have verified the following: There are multiple full backups for both,
> including one last night around 7pm. I did those manually. I also did 5
> transaction log backups in a row for each with no problem, 4 manually the
> final using a scheduled job. If I use SSMS and go to Tasks - Restore
> Database, the Full backup and all 5 Log Backups do populate in the grid of
> available backups to restore from, so the system is definitely seeing
> them.
> In msdb.dbo.backupset the only difference I note is that the username for
> the Full and first 4 tlog backups is SA and the last tlog backup is a
> domain admin account used by SQL Agent. I did try the above backup both
> using SA and the SQL Agent job. Both give same error. I have also
> verified that these two databases have not been restored since the last
> log backup.
> I checked profiler but it blanks-out all BACKUP operations for security
> reasons.
> Any ideas'
> Server is 2005, build 1366. Looks like I need to upgrade to latest
> service pack at some point, although I did review the fix lists for SP1
> and 2 and didn't see anything that would cause this.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Ouch this one doesn't sound like a fun one. Just an idea but are the full
backups possibly being run 'with copy_only'. If you have copy_only set I
wouldn't think it would show up in the grid for restores but its always
something to check. There's a bit column in msdb.dbo.backupset called
is_copy_only that you can check.
Good Luck!
-Mike
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n7m6plje6dv17@.corp.supernews.com...
>I have two databases that both give the following error when attempting to
>do a BACKUP LOG:
> Msg 4214, Level 16, State 1, Line 1
> BACKUP LOG cannot be performed because there is no current database
> backup.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> The backup statement is this:
> backup log [00000005]
> to disk='e:\MSSQL Backup\20071227\00000005_logbackup_20071
2271121.TRN'
> with name = 'e:\MSSQL
> Backup\20071227\00000005_logbackup_20071
2271121.TRN', description =
> 'Nightly Maintenance', password='thepassword'
>
> I have verified the following: There are multiple full backups for both,
> including one last night around 7pm. I did those manually. I also did 5
> transaction log backups in a row for each with no problem, 4 manually the
> final using a scheduled job. If I use SSMS and go to Tasks - Restore
> Database, the Full backup and all 5 Log Backups do populate in the grid of
> available backups to restore from, so the system is definitely seeing
> them.
> In msdb.dbo.backupset the only difference I note is that the username for
> the Full and first 4 tlog backups is SA and the last tlog backup is a
> domain admin account used by SQL Agent. I did try the above backup both
> using SA and the SQL Agent job. Both give same error. I have also
> verified that these two databases have not been restored since the last
> log backup.
> I checked profiler but it blanks-out all BACKUP operations for security
> reasons.
> Any ideas'
> Server is 2005, build 1366. Looks like I need to upgrade to latest
> service pack at some point, although I did review the fix lists for SP1
> and 2 and didn't see anything that would cause this.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Good point but per the OP I had already done this (via restorehistory table
even).
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eW3Q4vKSIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Check msdb..restorehistory to see if something is doing a restore
> operation that you are not aware of...I've seen this, but don;t recall the
> conditions or product
> --
> Kevin Hill
> IC3 North Texas
> www.ChristianCycling.com
> Please support me in the 2008 MS150:
> http://www.ms150.org/dallas/donate/donate.cfm?id=208000
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13n7m6plje6dv17@.corp.supernews.com...
>|||Another good check, but no luck. All rows in backupset showed that no
backups were copy only. Besides, I wouldn't have been able to do the 5 tlog
backups successfully prior to the one that failed if the full backup was
copy only.
I wish I could 'know' what happened between 8:22pm last night and 1am this
morning when the agent job kicked off and failed!! Being able to see
statement-level activity from within sql server for the backup log command
would be immensely helpful too darn it!
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Michael Abair" <mabair@.autotask.com> wrote in message
news:emsnlwKSIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Ouch this one doesn't sound like a fun one. Just an idea but are the full
> backups possibly being run 'with copy_only'. If you have copy_only set I
> wouldn't think it would show up in the grid for restores but its always
> something to check. There's a bit column in msdb.dbo.backupset called
> is_copy_only that you can check.
> Good Luck!
> -Mike
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13n7m6plje6dv17@.corp.supernews.com...
>|||Another guess from personal experience: Something switched the database to
SIMPLE mode then back to FULL mode.
If this is the case, a differential backup should be enough to reestablish
the LOG backups. (Then go hunting for what changed the database option. A
good target for a server level DDL trigger to catch the ALTER DATABASE
statement.)
FWIW,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n7m6plje6dv17@.corp.supernews.com...
>I have two databases that both give the following error when attempting to
>do a BACKUP LOG:
> Msg 4214, Level 16, State 1, Line 1
> BACKUP LOG cannot be performed because there is no current database
> backup.
> Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> The backup statement is this:
> backup log [00000005]
> to disk='e:\MSSQL Backup\20071227\00000005_logbackup_20071
2271121.TRN'
> with name = 'e:\MSSQL
> Backup\20071227\00000005_logbackup_20071
2271121.TRN', description =
> 'Nightly Maintenance', password='thepassword'
>
> I have verified the following: There are multiple full backups for both,
> including one last night around 7pm. I did those manually. I also did 5
> transaction log backups in a row for each with no problem, 4 manually the
> final using a scheduled job. If I use SSMS and go to Tasks - Restore
> Database, the Full backup and all 5 Log Backups do populate in the grid of
> available backups to restore from, so the system is definitely seeing
> them.
> In msdb.dbo.backupset the only difference I note is that the username for
> the Full and first 4 tlog backups is SA and the last tlog backup is a
> domain admin account used by SQL Agent. I did try the above backup both
> using SA and the SQL Agent job. Both give same error. I have also
> verified that these two databases have not been restored since the last
> log backup.
> I checked profiler but it blanks-out all BACKUP operations for security
> reasons.
> Any ideas'
> Server is 2005, build 1366. Looks like I need to upgrade to latest
> service pack at some point, although I did review the fix lists for SP1
> and 2 and didn't see anything that would cause this.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Crud...that line got a bit buried in the text.
Any Truncation of the T-log happening that would cause a variation in the
recovery path? Anything else in the ERRORLOG between working and broken?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n817an3aton89@.corp.supernews.com...
> Good point but per the OP I had already done this (via restorehistory
> table even).
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:eW3Q4vKSIHA.6036@.TK2MSFTNGP03.phx.gbl...
>