Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: SQLSVR 2005: Just no excuse for this...

    I created a maintenance plan for my 2005 server which included deleting old backups...or so I thought. The backups are stored in subdirectories, and the maintenance plan is not looking in subdirectories for old files.
    So, I check the sql that is being executed, and it runs a command called xp_delete_file. No problem, I'll just look up that parameters and see if there is an option for including subdirectories.

    NO CAN DO! XP_DELETE_FILE is an undocumented extended stored procedure. http://connect.microsoft.com/SQLServ...dbackID=124708
    Thanks for making it impossible to research my error, MicroSquash!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Found an option to delete first-level subfolders, but still no documentation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by blindman
    Found an option to delete first-level subfolders, but still no documentation.
    Try installing SP1. There's an option added to delete files from first-level sub folders.

    Regards,

    hmscott

    PS. I agree; no excuse on this one.
    Have you hugged your backup today?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How come maintenance plans? Do you not roll your own?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I had some home-rolled plans for 200, but not for 2005 yet.

    I just think it is lame for them to be scripting undocumented procedures in their code wizards.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    How come maintenance plans? Do you not roll your own?
    I never roll my own. Primarily because:

    1) I'm lazy;

    2) I'm not permanent; I want to keep things as simple as possible for whoever has to replace me;

    3) At 3:00am, I don't want to have to remember which variant of a backup strategy I'm currently dealing with.

    I create two Maintenance plans for every server: one for the system databases (which don't use the full recovery model) and one for user databases (which all use the full recover model).

    Full backups to disk every night; transaction log backups to disk every hour (or three in test/dev).

    Backups get run to tape on a separate schedule monitored by the LAN Admin (which I check regularly).

    It's not fancy; it's not very sophisticated. But it does work, it's easy to document and it's kept me in the good graces of the powers that be for several years now.

    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - thanks for that. I have been slowly replacing my maintenance plans with my own.

    1) Because I had a bit of slack time and it was a nice challenge.
    2) Maintenance plans are great & simple until they go wrong and you have to debug them
    3) Maintenance plans are not very flexible - however that is one of your plus points and I see where you are coming from.
    4) I've just realised - I'm thinking of more than just backups (stat updates, selective defragging etc). Are you? With backups I think you are probably right - the last thing you want is unnecessary complication.
    EDIT 5) I had thought the path to uber-admin-dome included a library of home rolled uber-plans.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by pootle flump
    4) I've just realised - I'm thinking of more than just backups (stat updates, selective defragging etc). Are you? With backups I think you are probably right - the last thing you want is unnecessary complication.
    EDIT
    Sorry, you're right; I should clarify. I only use maintenance plans for backups. For index defrags, re-orgs, etc. I tend to manage by exception (ie, check periodically but don't schedule anything).

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •