Can't Backup TLog? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Can't Backup TLog?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-11-15 : 19:49:25

I was just looking at the reports for my backup plan, and noticed that SQL Server is erroring when trying to backup the transaction log. Below is the executing line, and the resulting output. Is there anyway I can get more information from the process as to why the backup won't occur?

Also, normal DB backups seem to be unaffected, and work just fine.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 44F90890-49F8-4C00-B82D-F5970E250383 -Rpt "F:\Backups\SQL2K\Reports\dnd\dnd_MP_16.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\Backups\SQL2K\TransactionLogs\dnd" -DelBkUps 7DAYS -BkExt "TRN"'

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'DND' as 'DND\Administrator' (trusted)
Starting maintenance plan 'dnd_MP_1' on 11/15/2003 7:37:27 PM
Backup can not be performed on database 'dnd'. This sub task is ignored.

Deleting old text reports... 0 file(s) deleted.

End of maintenance plan 'dnd_MP_1' on 11/15/2003 7:37:27 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

I forgot to mention that for some reason the tlogs had been backing up just fine up until a day or two ago.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-16 : 06:34:14

Has the database been changed to simple recovery mode?
What happens if you try to backup the tr log manually?
Easier to get rid of the maintenance plans and code it yourself then you will know what is going on.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 12:18:06

You can find a better error in the SQL Server Error Log. But I recommend going down Nigel's approach. Try it manually:

BACKUP LOG DBName
TO DISK = 'F:\MSSQL\Backup\DBName.TRN'
WITH INIT

If you get an error, post it here so that we can help troubleshoot it. If you don't get an error, then recreate your maintenance plan or don't use maintenance plans as Nigel mentioned.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources