Backups to overwrite, not append
Posted on 2004-03-30
I have SQL Server 2000 running on a Win2K Server platform.
I use the SQL Server Agent > Jobs to schedule daily backups of my databases.
The backups are done to a hard drive on the network.
I have 7 backups, one for each day of the week, so that I can keep the last whole weeks data. Each days backup is done to a different file.
This is the T-SQL I use to run Mondays backup:
backup database myDB
retaindays = 6
The problem I am having is that when the next Mondays backup runs it APPENDS the backup to the old file, so my backup file gets bigger and bigger every week.
Can you show me the T-SQL that will OVERWRITE the old backup?