We use SQL Server 2000 with the latest service pack. Whenever I drop, insert
into or truncate a table via Query Analyser, I get an error reported by the hourly backup job
running on the server. The error is:
Cannot allow BACKUP LOG because file 'BDAS_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [SQLSTATE 42000] (Error 4213) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.How can I fix this error?
Requires Free Membership to View
It may sound like a silly question but I'll ask it anyway? Are you sure that you are using SQL Server 2000? If so, this should not be happening and you may have a corruption problem of some sort. Everything in SQL Server 2000 is logged in one way or another and should not break the transaction log backup sequence. See this url for more information: http://support.microsoft.com/default.aspx?scid=kb;en-us;272093
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation