I'm trying to schedule full and incremental backups with maintenance plans of database but it seems that It's not truncating logs of database so how can I do that in maintenance plan? Is there a option or do I have to schedule another script for that? asked 03 Apr '20, 04:54 hg1735 |
A maintenance plan is basically a UI that builds an event. You could modify the event to meet your specific needs. See BACKUP DATABASE statement for syntax of the statement used in the event to backup the database. To truncate the log, you will need to use the TRANSACTION LOG TRUNCATE backup option. answered 03 Apr '20, 09:54 Chris Keating |
In the box ro Run this SQL after the plan, you could put BACKUP DATABASE DIRECTORY '' TRANSACTION LOG ONLY TRANSACTION LOG TRUNCATE; COMMIT; answered 06 Apr '20, 08:47 mrmitch |
Do you really want to truncate the log when you run a backup transaction log only? Because if you do then you need to be aware that in case of a restore you need the latest full database backup and all separate backup logs made since the full backup.
If the size of the log is no problem, I would generally recommend to do the transaction log backups without truncation so the log grows until the next full backup but you only need the latest log backup for recovery...