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

SQL Server 7.0 – Transaction Log Not Backing Up

I have not worked with SQL Server 7.0 in a loooong time.  So when I received a call from a client complaining about their Transaction Logs not backing up, I was stuck for a few minutes.  My initial thought was check the recovery model, but as I jogged my memory about recovery models I remembered that they were a new feature introduced in SQL Server 2000.  I did on the other hand, remember that SQL Server 7.0 did have an option, Truncate log on checkpoint, that would prevent Backing up the Transaction Log.  See the image below:




After my brief walk down memory lane, I asked the client to check this and as expected it was enabled.  Once they disable the option, the backups began to work.  So for all of you out there that are still using SQL Server 7.0 here is something for you.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch

SQL Down South


Posted by cgodio on 14 September 2009

yes, i had the same problem.

Posted by audrey.hogan on 18 September 2009

What would you do if SQL Server 2000 Transaction Logs were not backing up?  I am having this issue.  Thanks, Audrey

Posted by Patrick LeBlanc on 21 September 2009

First run this.

select name, databasepropertyex(name, 'Recovery') as RecoveryModel from master.dbo.sysdatabases order by name

You can filter on your database name.  If the Recovery Model is simple you will need to change it to Full.  You can use Enterprise Manager or the following script, replace databasename with your database:



Hope this helps.  

Leave a Comment

Please register or log in to leave a comment.