Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Talking Unanswered: Stored Procedure for Changing Recovery Model ?

    The SQL Server is running full recovery model but they would like it to be changed to simple while the data backup occurs then set back to full. Is there a way to do this in a stored procedure or is it all done via options ? Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suppose that it could be done, but I can't for the life of me think of any reason why I'd do it. This just sounds like a bad plan to me!

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    14
    Originally posted by Pat Phelan
    I suppose that it could be done, but I can't for the life of me think of any reason why I'd do it. This just sounds like a bad plan to me!

    -PatP
    Yeah its not the most optimal situation I would like to see but its what they're asking for and can't talk them out of it. They have mirrored servers along with data backing up so they don't really need a full data transaction log anyways.....

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, it's actually your responsibility to put on your "Consultant" hat, analyze where the need is coming from and what's the real reason for "them" to ask for it. And after that, - present the "real" solution.

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    Originally posted by rdjabarov
    Well, it's actually your responsibility to put on your "Consultant" hat, analyze where the need is coming from and what's the real reason for "them" to ask for it. And after that, - present the "real" solution.
    Well, if you knew more of the design, reasoning and limitations presented then you would know why I am looking at the feasability of this solution. And really they're looking to change the log type while it automates the data transfer then turn it back to full. With the backup integrity they already have a temporary change would not overly affect anything. So really you're not helping much with towards the "real" solution. But thanks anyways.

  6. #6
    Join Date
    Mar 2004
    Posts
    14
    For anyone looking to do this in the future the command is as follows

    ALTER DATABASE <database_name> SET RECOVERY [FULL | BULK_LOGGED | SIMPLE]

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just curious, but what value do your users see in switching recovery models during the backup? Since this effectively renders the recovery model at the lowest level it is ever set, there is no benefit that I can see in programmically changing it, even though it is possible to do that. That is why I've never done it.

    My biggest concern is that your users may think they'll get something quite different than what the SQL Engine can actually deliver. Users often base their decisions/demands on marketing information that is quite correct as published, but woefully wrong as implemented.

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I have a process that does programmatic change from SIMPLE to FULL, but not the other way around. The reason is because we base our backup job on Database Maintenance plans and SQLLiteSpeed. Since SQLLiteSpeed cannot "read" the plans (yet), I wrote a couple of procedures that read the contents of 3 pre-defined plans (system, application, and applog), creates a temporary batch file by BCPing commands into it, checking if while attempting to do trxlog dump the database is in FULL recovery mode (and if not - changing it to FULL/trunc. on chk=true for 7.0), and finally CALLing above temp batch file.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You I'm not worried about. You're a bright boy (when you aren't dying for a cigarette anyway), and understand exactly what the implications of your choices are. I'm concerned about users who see a comment in a flyer or flashed up in a PowerPoint presentation, and suddenly have great ideas about how they want to manager their database...

    While the point in the brochure or presentation was probably technically correct (Microsoft is usually good about getting the tech guys to approve what Marketing creates), I'm concerned that Joe User may not have either understood the implications of the point, remembered the technical details, or applied them correctly. Between the point the user absorbs the material and the point they demand it be used, there is a lot of room for error.

    If they have a propeller-head to vet their ideas, then I'm Ok with it. The users often have great ideas, and if they can actually happen, that is a great thing. They need somebody technically knowledgable to determine if the great idea is feasible.

    -PatP

Posting Permissions

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