March 15, 2004 at 6:41 am
Greetings all, I'm trying to create a backup of a database that I have on my PC. I've done this:
BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,
NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,
RETAINDAYS=5
DECLARE @i INT
select @i=position from msdb.backupset where database name='mydb'
and type!='F' amd backup set id=(select max(backupset set id) from
msdb.backupset where database name='mydb')
RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@i
This script was created automatically, but I added the RETAINDAYS bit. I only want the past five days of backups available, but when I check the list, I've got backups dating back to December 2003! The older backups are correctly being marked as expired, but they are still on the list! Does that mean they are still on my hard drive?! How can I purge the list to only have the last five days of backups?
Thanks in advance
Johnny
March 15, 2004 at 8:07 am
The issue is that you are saving all the backups to the same file. SQL Server has to delete all or nothing. So, let's say you have RETAIN 1 day.
You backed up yesterday to mybackup.bak. Today you backed up to the same file. The file now has today's date, not yesterdays. SQL Server can't delete it.
Search on this site, especially go to the forum for BACKUPs, and you should find how to save backups to files based on the date.
-SQLBill
March 15, 2004 at 8:28 am
Thanks for that, it explains the problems I've been having.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply