Backup issues

[dhulbert]

BACKUP DATABASE [LIVE] 
TO  DISK = N'E:\Backup\Live_Backups\Live_Backup.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'LIVE-Full Backup', 
SKIP, NOREWIND, RETAINDAYS = 3, NOUNLOAD, STATS = 10

I'm running the command above to create my nightly full backup, but rather than keeping the last 3 backups it's got at least 12 in there. I've obviously got something screwy, any ideas why it's not clearing out the old backups?

Cheers.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SQLBill]

From the BOL for RetainDays:
[quopte]
Specifies the number of days that must elapse before this backup media set can be overwritten.
[/quote]
The important thing to note is the word 'can'. It can be deleted after those amount of days, but it doesn't mean it will be. I would guess your tape or drive is not full yet, so it doesn't have to overwrite the files. Without retaindays, when the tape/disk got full or didn't have enough room left you would get the error 'tape/disk is full'. With retaindays, it will overwrite files once the tape/disk is full.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dhulbert]

So as I'm backing upto a disk with massive amounts of space then it's just going to keep growing.
Is there anwyaw to automatically clear down any backup's after a fixed amount of time?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[mrdenny]

Don't backup to the file name every time, then use the xp_delete_file extended stored procedure in the master database to delete the files that you don't need to keep around.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[SQLBill]

Like MrDenny said (always good advice from him)...and you could do something like have code that adds the date to the end of the backup file name.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875