SQL Server Transaction Log Backup File

0
votes

I am using SQL Server 2016.

I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily).

Backup transaction log into a single file using:

BACKUP LOG [XXX] TO  DISK = N'E:\SQLDB\Backup\XXX_Trans_Log.bak' WITH RETAINDAYS = 28, NOFORMAT, NOINIT,  NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

What I expected is only 28 days transaction log backups will be kept. But now I just found All transaction log backups are kept. So the file grows to very large in size.

Is there syntax/option problems in the backup statement?

Or I should have to store in separate files?

What should I do now?

1
sql-serverbackuptransaction-log
Almost ALWAYS it is better to create a separate file for each backup because it is easier to see your backups and easier to access them (since each one must be restored in sequence if needed). - SMor

1 Answers

0
votes

retaindays does not do what you are expecting it to do.

It prevents backup files from being overwritten, but it does not mean that old backups are automatically deleted.

See here

If you want a solution that will do both backup and cleanup, try Ola Hallengren's solution

PS: This question is better suited to https://dba.stackexchange.com/