Backup Log with retaindays

Question

Backup Log with retaindays

bigblue-283639

SSC-Addicted

Points: 468
More actions
December 6, 2005 at 10:01 am

#152653

Hi,
I am new to SQL server. Trying to set up a backup job (backup log with retaindays) using EM. The job kept failing and the log is not much of help.
Could anyone please tell me is there anything wrong here? thanks in advance!

BACKUP LOG [model] TO [model BackupLog] WITH INIT , NOUNLOAD , RETAINDAYS = 3, NAME = N'model backuplog', NOSKIP , STATS = 10, NOFORMAT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vera Maras SSC Enthusiast Points: 119 More actions · Answer 1

Hi.

I am not an expert, but I would like to suggest you to check the combination of WITH INIT and NOSKIP while doing backup with retaindays.

INIT means that you want to rewrite old backup sets, NOSKIP that you want to check if there is any old backup set on the media which should not be overwritten.

Of course, with RETAINDAYS you tell it for how long to keep old backup sets.

It seems like a conflict to me. Maybe you should try with NOINIT?

Does your first backup succeeded?

bigblue-283639 SSC-Addicted Points: 468 More actions · Answer 2

Hi Vera,

Thanks for your reply. You are right: My first backup succeeded. But the second, and the third, ... all failed. If I try with NOINIT, which basically means DO NOT overwrite the old backup sets, what would happen after the third day (retaindays=3)? Because at the beginng of the fourth day , I do want the Backup job to overwrite the backup sets from the previous 3 days and start all over again ... But I will try and see what happen, will let you know what I find out ...

Thanks,

bigblue-283639 SSC-Addicted Points: 468 More actions · Answer 3

Hi Vera,

The backup job succeed after I used the 'with NOINIT' parameter, however, old transaction logs (older than 3 days) are NOT being purged/deleted/overwritten after the specified 3 days. I can still see these files in the backup device (via view Backup Device Media content) and the 'expiration date' clearly indicates 12/09/2005. I must be doing somethign wrong here??

SQLBill SSC Guru Points: 51440 More actions · Answer 4

RETAIN DAYS and EXPIRE only work for tape backups or backups that are saved with dates for the file name. If you keep reusing the same backup file, RETAIN DAYS and EXPIRE won't work. I use two jobs. Job one runs once a day and uses INIT. Job two runs every four hours and uses NOINIT. That way, my first job of the day deletes the previous file and then the rest just append to the first job.

-SQLBill