SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backup Problem


Transaction Log Backup Problem

Author
Message
Bob Shaw
Bob Shaw
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 231
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?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39642 Visits: 38560
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?

Cool
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)
Bob Shaw
Bob Shaw
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 231
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39642 Visits: 38560
Verify the recovery model of the database. Also, run this query: select * from msdb.dbo.backupset where backup_start_date > '20090602'

Cool
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)
Bob Shaw
Bob Shaw
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 231
Recover model is set to FULL and the query you gave me pulls 66 records and the database in question is in the results.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39642 Visits: 38560
What is the value of the is_copy_only column?

Cool
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)
Bob Shaw
Bob Shaw
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 231
the value in that column is 0.
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 8586
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39642 Visits: 38560
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.

Cool
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)
Bob Shaw
Bob Shaw
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 231
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search