Post reply

Set recovery full for multiple database

  • July 12, 2011 at 3:03 pm

    #254398

    Hi,

    I want to perform

    ALTER DATABASE dbname SET RECOVERY FULL

    for 20 databases using single script.

    Please advice..

  • July 12, 2011 at 3:11 pm

    #1351911

    if its all your user databases:

    select 'alter database '+name+ ' set recovery full' from master.sys.databases where database_id > 4

    execute in text mode

    copy and paste results to another window and run

    then take a full or diff backup to start your lsn chain off.

    ---------------------------------------------------------------------

  • July 12, 2011 at 3:33 pm

    #1351917

    we have 100 90 databases and out of them 20 should be in FULL recovery. Is there way to set this in single step?

  • July 12, 2011 at 3:37 pm

    #1351920

    Yes >>>

    george sibbald (7/12/2011)

    if its all your user databases:

    select 'alter database '+name+ ' set recovery full' from master.sys.databases where database_id > 4

    execute in text mode

    copy and paste results to another window and run

    then take a full or diff backup to start your lsn chain off.

  • July 12, 2011 at 4:16 pm

    #1351930

    unless the 20 databases have something in common you can use in a where clause on sys.databases just execute the 20 relevant statements from the full list produced by the query above.

    which is what ninja was alluding to. 🙂

    ---------------------------------------------------------------------

  • July 12, 2011 at 4:17 pm

    #1351931

    how many databases on the server are 90 compatibility

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 12, 2011 at 4:54 pm

    #1351937

    Just BTW, doing that by hand should take you less than 10 seconds per DB. So that's 4 minutes if you include reading this message in your mail, then here and then back to ssms.

    You seem to already know what dbs should be included. So if the only filter you can set is to manually name each of the DBs in the query then use the wizard. Otherwise put the correct filter and use the query / paste / run option.

    Edit : Corrected typo.

  • July 13, 2011 at 2:18 am

    #1352000

    adding 'order by name' to the query might make it a bit easier to find the correct database statements

    ---------------------------------------------------------------------

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

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