5

Using a command similar to:

ALTER DATABASE test0402er2Y8 SET RECOVERY FULL
GO

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Thanks

Bill
  • 273
  • 2
  • 4
  • 7

3 Answers3

9

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Below T-SQL will help you. It will check for ONLINE databases with SIMPLE recovery model and will print TSQL to change it into FULL Recovery mode. Run below code in TEXT Mode -- SSMS CTRL+T. Once you are happy, run the output generated from another session :

set nocount on
go

if exists (
        select 1
        from sys.databases
        where recovery_model_desc = 'SIMPLE'
            and state_desc = 'ONLINE'
        )
begin
    print '-- You are setting up database to FULL recovery mode. '
    print '-- Make sure you take first full backup and then schedule LOG BACKUPS for proper transaction log maintenance !'

    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET RECOVERY FULL with ROLLBACK IMMEDIATE;'
    from sys.databases
    where recovery_model_desc = 'SIMPLE' -- since you only want SIMPLE recovery model databases to get changed to FULL recovery.
            and state_desc = 'ONLINE'
end
else
    select 'ALL the databases are in FULL RECOVERY MODE - Make sure you take proper LOG BACKUPS !!'

NOTE : For any databases that are newly created, its better to change the recovery model of model database to FULL, so that the new databases that are created inherit the FULL recovery setting from model database.

Worth mentioning : Why Does the Transaction Log Keep Growing or Run Out of Space?, since you are going to switch to FULL recovery and if you dont take care of your transaction log, then you will end up with a bloated transaction log. Read the answer linked for more details.

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245
3

You can use Cursor for that like this:

DECLARE @DBName VARCHAR(255)
DECLARE @SQL NVARCHAR(255)

DECLARE FULLRECOVERY CURSOR FOR   
SELECT name  
FROM sys.databases WHERE database_id > 4 and recovery_model_desc = 'SIMPLE'

OPEN FULLRECOVERY 
FETCH NEXT FROM FULLRECOVERY INTO @DBName 
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL' 
    PRINT @SQL 
    EXECUTE sp_executesql @sql 
    FETCH next FROM FULLRECOVERY INTO @DBName 

END 

CLOSE FULLRECOVERY 
DEALLOCATE FULLRECOVERY

More info about this script I wrote my post here: http://www.pigeonsql.com/single-post/2016/12/20/How-to-Change-Recovery-Model-to-Simple-or-Full-for-All-Users-databases

Filip Holub
  • 179
  • 3
1

What if you use sp_msforeachdb like

exec sp_msforeachdb 'alter database ? set recovery full'

Here's a Nice Script written using cursor

Rahul
  • 143
  • 2