Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    56

    Question Unanswered: SQL 2005 backup maintenance plan

    Hi there

    I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.

    Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(

    Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.

    Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?

  2. #2
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by Mynx
    I've been using SQL 2000 for about 5 years now ... I'm in no way an advanced user ...
    OK, but you should consider becoming one, after 5 years. Scripts are much more reliable and flexible than the maintenance plans, and they're really not that hard to write. Seriously.

    ...let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place...
    I'd be very interested to know what that key was and what change you made. I've never heard of such a thing. Can you post that info?

    Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
    If you're managing a SQL 2000 box from the SQL 2005 Management Studio, then yes, that's correct. 2000 is legacy as far as 2005 is concerned.

    Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
    Can't say I have had this issue, but I was able to find quite a bit of info in Books Online just by searching on 'Maintenance Plan'. Have you checked there?

    Personally, I'd recommend scripts. There are copious examples online, all over the place. Just cut and paste, change some of the parameters to your own, and away you go!

    Good luck.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Hope you have tidbits from the reply, in this case you can take help of Books online for SQL 2005 by looking at How-To topics;
    http://msdn2.microsoft.com/en-us/library/ms187510.aspx
    http://sqlserver-qa.net/blogs/tools/.../how-do-i.aspx too
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Mar 2007
    Posts
    56
    Quote Originally Posted by ReadySetStop
    I'd be very interested to know what that key was and what change you made. I've never heard of such a thing. Can you post that info?
    In the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer (or similar - don't have access to pc at the moment) I added a string value "BackupDirectory" with the value "D:\"

    But besides that I've downloaded SP2 which may address the problem of the maintenance plan - which I can only test when I have access to the pc.

    On a side note, said pc with SQL 2005 is being used for a big database conversion. I previously tested the database conversion with SQL 2000 and it ran for 3.5 days. SQL 2005 4.5 days and counting.... :-(

  5. #5
    Join Date
    Mar 2007
    Posts
    56
    Ok so I've found out what I did wrong. When I installed SQL2005 I had MSDE already installed so what apparently happened is that SQL2005 creates the local instance and is just a "front-end" to the MSDE, i.e. it DOES NOT upgrade! Ok - I'm trying here - I'm not a technical guru - I just write scripts. Would've been nice to have that in some sort of documentation... That would also explain why my database conversion took so long - it was using MSDE and not full SQL2005. Can I have a double "awwww shame" here?

    So to fix it guys - if anyone else ever has the same problem - you need to install a new instance and THAT would be proper SQL2005. I did that and NOW I can see the Maintenance Plans and can see the option to run the backup wizard. I haven't tested it yet but I'm assuming it will work. *sigh*

Posting Permissions

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