Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: Set Options Loop for all DB's

    We use the following script to loop through all of our test db's (300+) when testing.
    Is there a way to have it change the recovery (simple to bulk logged etc.) method as well?

    ALTER DATABASE won't work with the variable @DBName

    Thanks in advance!




    /* Begin Script */
    SET QUOTED_IDENTIFIER OFF
    USE master

    DECLARE @DBName CHAR(64)

    DECLARE DBCursor CURSOR FOR
    SELECT name
    FROM master..sysdatabases

    OPEN DBCursor

    FETCH NEXT
    FROM DBCursor
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN


    PRINT ''
    PRINT '----------------------------------'
    PRINT 'DATABASE ' + @DBName
    PRINT ''

    exec sp_dboption @DBName

    /* Preform Set Option AUTO-SHRINK
    PRINT 'Setting option NO autoshrink on ' + @DBName
    EXEC sp_dboption @DBName, 'autoshrink', 'FALSE' */

    /* Preform Set Option AUTO-CLOSE
    PRINT 'Setting option YES autoclose on ' + @DBName
    EXEC sp_dboption @DBName, 'autoclose', 'TRUE' */

    /* Preform Set Truncate on Checkpoint
    PRINT 'Setting to truncate on checkpoint on ' + @DBName
    exec sp_dboption @DBName,'trunc. log on chkpt.','on' */


    /* DBCC SHRINKDATABASE (@DBName) */
    PRINT 'Shrinking on ' + @DBName
    DBCC SHRINKDATABASE (@DBName, 10)

    /* ALTER DATABASE @DBName
    SET RECOVERY BULK_LOGGED */


    FETCH NEXT
    FROM DBCursor
    INTO @DBName
    END


    CLOSE DBCursor
    DEALLOCATE DBCursor

    /* Finished */

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Use EXEC
    Code:
    DECLARE @db varchar(30)
    SET @db = pubs
    EXEC ('ALTER DATABASE ' + @db + ' SET RECOVERY SIMPLE')
    MCDBA

Posting Permissions

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