Backup w/RETAINDAYS just keeps growin', and growin'

Hi all;

I need to make daily backups to a computer in another domain, where I can't map a drive, so I use the TSQL to create a dump device, which works fine:

EXEC sp_addumpdevice 'disk', 'MyBackupDevice', [ipaddress & directory/file]

Then I schedule a server job which faithfully backs up my database, with one hitch - it never deletes days older than 5 as per the code - it just keeps growing. The command couldn't be much simpler:

BACKUP DATABASE ProductionETC to MyBackupDevice WITH RETAINDAYS = 5

For now, I just create a new backup device every now and then and go back and forth, deleting the oldest one when I create a new one. But it's annoying! Any ideas what I'm doing wrong?

Thanks,

L. Rap

RETAINDAYS only prevents SQL Server from overwriting the file unless the specified number of days have elapsed. It doesn't mean that SQL Server will automatically delete the file or erase the media after the specified number of days. So you will have to delete the files using your own scheduled job or use a database maintainence task (you can create from Enterprise Manager or SSMS).|||

Thanks Umachandar, that's very helpful. I misunderstood the instructions.

Would you happen to know if it is possible to delete the earliest backup from the set using T-SQL or some other preset command, or if I need to schedule an OS job to simply delete the file and start over?

Regards,

L. Rap

Backup using TSQL Question

Ladies / Gentlemen
When I issue the command to 'BACKUP' my Database it does so
with no problem.
However it appends to the last backup. How do I make it
overwrite it.
I am using the following code
if not exists (select *
from master.dbo.sysdevices
where name = 'SACS2' )
begin
USE master
EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SACS2.dat'
end
BACKUP DATABASE SACS TO SACS2
Mark MossMark Moss wrote:
> Ladies / Gentlemen
>
> When I issue the command to 'BACKUP' my Database it does s
o
> with no problem.
> However it appends to the last backup. How do I make it
> overwrite it.
>
> I am using the following code
> if not exists (select *
> from master.dbo.sysdevices
> where name = 'SACS2' )
> begin
> USE master
> EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft S
QL
> Server\MSSQL\BACKUP\SACS2.dat'
> end
> BACKUP DATABASE SACS TO SACS2
>
> Mark Moss
>
Include WITH INIT in the backup command, or write to a different file...
Found this in Books Online, under the BACKUP command...|||Try:
BACKUP DATABASE SACS TO SACS2 WITH INIT
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:efN9mkGnGHA.4620@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> When I issue the command to 'BACKUP' my Database it does so
> with no problem.
> However it appends to the last backup. How do I make it
> overwrite it.
>
> I am using the following code
> if not exists (select *
> from master.dbo.sysdevices
> where name = 'SACS2' )
> begin
> USE master
> EXEC sp_addumpdevice 'disk', 'SACS2', 'c:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\SACS2.dat'
> end
> BACKUP DATABASE SACS TO SACS2
>
> Mark Moss
>