July 18, 2005 at 11:35 am
Here’s what I’m trying to do – set up a once-a-week backup that is stored for 8 weeks before the oldest backup set is overwritten. This should allow us to use the same backup device indefinitely without causing diskspace issues.
The problem I’m running into is that SQL Server will not overwrite any medium in the device until all medium in the device have expired. The statement below works fine if I use WITH NOINIT but the file grows until it uses all available disk space. As soon as I change to WITH INIT, it fails stating that the (latest) backup doesn’t expire until such-and-such a date.
Is it possible to modify the backup database statement so that new backups are appended until there is an expired medium (which can then be overwritten)? Perhaps I’m expecting too much?
Failing that, is it possible to programmatically switch to another backup device?
We were trying to avoid doing a backup to DVD but if we can’t overwrite just the oldest medium in the device, it may be our only choice.
BACKUP DATABASE [Construction] TO [test] WITH INIT, RETAINDAYS = 56, NOUNLOAD , NAME = N'TLM32 Friday backup test', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='Construction'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='Construction')
RESTORE VERIFYONLY FROM [test] WITH FILE = @i
July 18, 2005 at 12:08 pm
RETAINDAYS is intended for TAPE backups. When backing up to disk, each backup needs to go to a different file. For example, each backup file could be saved with it's name as the date and time of the backup. If you append, then the file takes the date and time of the last backup; and it's the file date and time that is used by RETAINDAYS.
-SQLBill
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply