SQL Server database backup to store only the last 7 backups in a file I want to create a scheduled task that will run (every night) a SQL script to fully backup my database X to a file. I want that file to store up to 7 full backups. The way I tried doing this is using the following code:

SELECT @SQL = '
BACKUP DATABASE [X] 
TO  DISK = N''\file.bak'' 
WITH NOFORMAT, NOINIT, NAME = N''name'', RETAINDAYS = 7
'


But this doesn't overwrite/delete full backups contained on that file that are older than seven days.

Any ideas? Suggestions for alternatives?

Thanks
I would recommend you to study and implement  Ola Hallengren's SQL Server Backup solution. Its free and is widely tested and recognized in the community.

e.g :

Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24


Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script will give you a good start.