Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction Log Backup Problem Expand / Collapse
Author
Message
Posted Tuesday, June 2, 2009 1:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 10:46 AM
Points: 45, Visits: 176
Im trying to perform a transaction log backup to the same *.bak file holding the Full Database Backup. Im getting this error "BACKUP LOG cannot be performed because there is no current database backup."

I opened the backup file to view its contents and the the database is in there as a FULL backup.

When the database backups run it's marking the expiration date as 7 days from the execution date. I'm also backing up the master and msdb in the same backup file.

The backup order goes like this:
1. master (full backup)
2. msdb (full backup)
3. Our Application Database. ( full backup unless the database name is found in the backup file then it uses a Transaction Log Back instead)

Anyone else run into this situation? Is it possible to store a FULL and Transaction Log backup in the same Backup File?
Post #727696
Posted Tuesday, June 2, 2009 2:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 23,396, Visits: 32,230
Bob Shaw (6/2/2009)
Im trying to perform a transaction log backup to the same *.bak file holding the Full Database Backup. Im getting this error "BACKUP LOG cannot be performed because there is no current database backup."

I opened the backup file to view its contents and the the database is in there as a FULL backup.

When the database backups run it's marking the expiration date as 7 days from the execution date. I'm also backing up the master and msdb in the same backup file.

The backup order goes like this:
1. master (full backup)
2. msdb (full backup)
3. Our Application Database. ( full backup unless the database name is found in the backup file then it uses a Transaction Log Back instead)

Anyone else run into this situation? Is it possible to store a FULL and Transaction Log backup in the same Backup File?


The error you have received indicates that a full backup of the database needs to be accomplished before you can run a transaction log backup of the database. Have you recently changed the recovery model of the database in question?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #727719
Posted Tuesday, June 2, 2009 2:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 10:46 AM
Points: 45, Visits: 176
No, the database isnt changing. The backup is running in a application and for troubleshooting the problem.

I deleted the backup file so a new one will be created when the application runs.

Ran the application to create a backup.

Checked the backup file contents to make sure the database backup is in the backup file.

Then I ran the application backup routine again so it will perform a TL backup.

I still get the error. The application is perform the merge replication sync so the backup execute before each sync attempt. Thats why Im trying to use TL backups so the backup file size doesnt get out of hand and to be able to restore to a specific time.
Post #727728
Posted Tuesday, June 2, 2009 2:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 23,396, Visits: 32,230
Verify the recovery model of the database. Also, run this query: select * from msdb.dbo.backupset where backup_start_date > '20090602'



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #727737
Posted Tuesday, June 2, 2009 3:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 10:46 AM
Points: 45, Visits: 176
Recover model is set to FULL and the query you gave me pulls 66 records and the database in question is in the results.
Post #727748
Posted Tuesday, June 2, 2009 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 23,396, Visits: 32,230
What is the value of the is_copy_only column?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #727763
Posted Wednesday, June 3, 2009 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 10:46 AM
Points: 45, Visits: 176
the value in that column is 0.
Post #728250
Posted Thursday, June 4, 2009 9:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 902, Visits: 7,238
Is there a chance that the full backup procedure does an explicit truncate on the log at some point after the backup is taken? It would invalidate the use of the full backup as a staring point for rolling forward with the logs. Not a very likely scenario, but it's the only thing I can think of that might explain the problem.



And then again, I might be wrong ...
David Webb
Post #729089
Posted Thursday, June 4, 2009 10:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 23,396, Visits: 32,230
David Webb (6/4/2009)
Is there a chance that the full backup procedure does an explicit truncate on the log at some point after the backup is taken? It would invalidate the use of the full backup as a staring point for rolling forward with the logs. Not a very likely scenario, but it's the only thing I can think of that might explain the problem.


Good thought, David. I hadn't thought about that myself.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #729104
Posted Thursday, June 4, 2009 10:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 10:46 AM
Points: 45, Visits: 176
That was the issue, there was a call to shrink the database files using NoTruncate so they wouldnt hog up the hard drive space. But right after that though there was another call to truncate the log.

I removed the truncate log call and the backups are running fine.

Thank You so much for the help Lynn and David.
Post #729125
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse