Post reply

Backups

  • April 7, 2005 at 11:55 am

    #166541

    We have 15 databases on one server and having maintenance plan to take backups (full and transaction) and keeping last three days files.

    Problem: We don't want three days copies for all databases, some fo them needed 4 days, 3 days, 2 days depending on type of database.

    Is there any way to do this with scripts ( having retention period info in a table)

    thanks in advance.

  • April 7, 2005 at 1:07 pm

    #551281

    If you have only one maint plan for all db then it's difficult though not impossible but it's a pain.

    I would create separate maint plan for each database and select the file retention as per the requirement for each databse.If you already have separate plans then modify the plans as per your need.

    hth

    jp

     

  • April 7, 2005 at 1:10 pm

    #551283

    Thanks for your reply.

    The problem is i cannot create 15 maint palns because it is difficult to maintain. I am looking for a script (and a table) to do this. Any ideas please.

    Thanks.

     

  • April 7, 2005 at 2:00 pm

    #551298

    Sorry I do not have ready script but you can try using xp_getfiledetails with xp_cmdshell and storing the info in temp table.Then you can loop thru the info and identify the file that needs to be deleted based on the criteria for each db.You may need to build dynamic sql to delete the file.

    hth

    jp

  • April 7, 2005 at 3:30 pm

    #551320

    That's what we're doing now.  I have a table in my "DBA" database on each server that has a row for each database.  Controls whether backups are taken of the particular database, where it is placed, and retention.  All automated.  If you put a new database on the server today, tomorrow there will be a new row in the table for it, with default information, a new folder on the disk for the database, and backups will have already started.  If you'd like to see it, send me a private message with your e-mail address and I'll send it to you.  There are numerous stored procedures involved, but you could implement parts of it without the rest.

    Steve

  • April 8, 2005 at 10:02 am

    #551591

    15 databases is too much work ???

     

    I've got 19+ servers and 200+ databases all using maintenance plans (the only multiple database plans deal with master,model and msdb). Sure there's initial setup time (all part of a documented process), but after that no headaches. Add in an MSX server and multiserver JOBs and it's easy to manage as well.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • April 8, 2005 at 10:50 am

    #551626

    I hate maintenace Plans. Even though you get a nice GUI there is no way to SCRIPT them and that helps a lot for server rebuilds!


    * Noel

Viewing 7 posts - 1 through 6 (of 6 total)

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