Subscribe to this blog
Briefcase
Print
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:
ALTER DATABASE <DATABASENAME>
SET RECOVERY FULL.
Hope this helps.