How to store certain number of backups in backup file

Question

I have everyday schedule to backup my database in sqlserver 2005. Now it's looks like this:

BACKUP DATABASE [db1] TO  DISK = N'D:\SqlServer\Backup\db1.bak' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'db1backup',  NOSKIP ,  STATS = 10,  NOFORMAT

But in this case, it's will grow infinitely and I want to store only last 7 backups in file. How can I do that(maybe erase old backups somehow)?

Answer 1

Assuming you want to re-initialize the file every sunday (you can change this to your favorite day of the week) you can use the following:

declare @init_option nvarchar(50)
declare @cmd nvarchar(1000)
set @init_option = 'NOINIT'
IF (datename(dw, getdate())) = 'Sunday' set @init_option = 'INIT'
set @cmd ='BACKUP DATABASE [db1] TO  DISK = N''D:\SqlServer\Backup\db1.bak'' WITH  ' + @init_option + ' ,  NOUNLOAD ,  NAME = N''db1backup'',  NOSKIP ,  STATS = 10,  NOFORMAT'
EXECUTE(@cmd)