Post reply

2005 Backup retention

  • August 27, 2007 at 6:14 am

    #174415

    Hoping someone can clear this up for me..

    We are basically still a SQL Server 2000 shop but recently rolled out 2005 for one particular project.   Trying to get up to speed on 2005 and I am in the process of setting up Full and Transaction log backups for my ONE User Database using the Maintenance Plan wizard.

    couple of questions:   Should I set up two plans?  one for the Full Backup and one for the Transaction log backups??  Or should I make it one plan with two steps?

    Also,  I cannot figure out how to set a retention for the backups.  in 2000 there was a check box for 'delete files older than x days' but I cannot find this in the 2005 maintenance wizard. 

    I found this in BOL

    SQL Server 2005 Books Online
    How to: Set the Backup Retention Duration (SQL Server Management Studio)

    Use the media retention option to provide a system-wide default for the length of time to retain each backup set. The media retention option helps protect backups from being overwritten until the specified number of days has elapsed. When you set media retention, you do not have to specify the length of time to retain system backups each time you perform a backup. The default is 0 days. If you use the backup medium before the set number of days has passed, Microsoft SQL Server issues a warning message. SQL Server does not issue a warning unless you change the default.

    So that this mean that I can ONLY set the bCKUP retention SERVER WIDE IN 2005???

     

  • August 27, 2007 at 8:08 am

    #729536

    Question 1:

    One or two maintenance plans, I would say if you're not on SP2 you won't have much choice other than two plans. After SP2 you can create several subplans with each it's own schedule.

    Question 2:

    In 2005 you have to do 2 things. First define the exiprationdate/retaindays for your backup file and second you need to select a "Maintenance Cleanup Task" for removing old database files and/or logfiles.

    Question 3: Yes, media retention is a system wide setting, but you still can set the expiredate/retaindays for each backup. The server wide setting only applies if nothing else has been defined for the backup and I wouldn't change it.

    Hope this helps

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • August 27, 2007 at 8:37 am

    #729563

    I'd do two plans, otherwise, follow Markus' advice.

Viewing 3 posts - 1 through 2 (of 2 total)

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