SQL Server database backup to store only the last 7 backups in a file

Question

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

Answer 1

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.

Answer 2

The RETAINDAYS option maps to the Backup set will expire in x days option in SSMS. See my previous answer on this here