March 1, 2010 at 12:29 pm
I get the following error message when I try to backup a transaction log:
[font="Courier New"]Msg 4214, Level 16, State 1, Procedure MyProc, Line 44
BACKUP LOG cannot be performed because there is no current database backup.[/font]
However, I took a full backup yesterday morning. The backup_finish_date is 2010-02-28 10:50:41.000
I don't normally have any problems backing up the transaction log. The only thing I have found that is out of the ordinary is the presence of a snapshot. It was created by one of the developers yesterday. The create_date is 2010-02-28 11:42:23.380.
The last time the database was restored was nearly 3 months ago (2009-12-12 15:16:04.330). I was aware that restoring a db would break the log chain, but this db was not restored. Does merely creating a snapshot interfere with the transaction log in some other way? Am I barking up the wrong tree?
March 1, 2010 at 12:33 pm
Several things to check. First, was the log truncated (BACKUP LOG WITH TRUNCATE_ONLY) or was the database changed from FULL recovery model to SIMPLE recovery model and back? Both of these actions will break the log chain and require another full backup.
Check all maintenance plans that may be running as well.
March 1, 2010 at 12:35 pm
Someone's either run Backup Log with truncate only or has switched the DB into simple recovery and back. Those two things will break the log chain.
Both will be logged to the SQL error log. Check there, you'll be able to see when it happened and what SPID ran the command. Personally, I'd ask the developer who created the snapshot if he did anything else to the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2010 at 12:35 pm
The question that I have is this:
Why in the world would a developer be allowed to create a backup, or to restore a database from a backup?
A developer should have very minimal access, and not be able to perform any database administrative tasks.
Andrew SQLDBA
March 1, 2010 at 12:38 pm
And in answer to the question, Snapshots should not interfere with the log chain.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2010 at 12:45 pm
Dave Mason (3/1/2010)
The last time the database was restored was nearly 3 months ago (2009-12-12 15:16:04.330)...
It looks like I was wrong. The database was restored yesterday from the snapshot. So that's where the transaction log chain was broken. I found the evidence in the Sql log: Reverting database 'MyDB' to the point in time of database snapshot 'MyDB_Snapshot' with split point LSN ...
Why wasn't that same info in msdbo.dbo.restorehistory? This query misled me:
[font="Courier New"]SELECT MAX(restore_date)
FROM msdb.dbo.restorehistory
WHERE Destination_Database_Name = 'MyDB'[/font]
March 1, 2010 at 12:52 pm
Because restore history is the restore from backup history, not reverting to a snapshot.
Is this a production server? If so, why is a dev creating and reverting snapshots?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply