Generate dynamic script

Question

Generate dynamic script

patel7

SSC Veteran

Points: 273
More actions
August 19, 2011 at 11:40 am

#246608

Hello,
I want to perform the below action on 150 databases in the same sql instance
ALTER DATABASE [DB_NAME] SET RECOVERY SIMPLE
How to create a dynamic script for this to change the recovery to simple for 150 databases ?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's something i use on my dev server...don't need anything in FULL recovery mode on that server at all...it's a play ground.:

USE MASTER

declare

@isql varchar(2000),

@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

open c1

fetch next from c1 into @dbname

While @@fetch_status <> -1

begin

select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

select @isql='USE @dbname checkpoint'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

fetch next from c1 into @dbname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patel7 SSC Veteran Points: 273 More actions · Answer 2

Thanks Lowell,

In above script, I want to check the current recovery model of the database and then set to SIMPLE.

Please advice.

Lowell SSC Guru Points: 323518 More actions · Answer 3

probably easiest to simply change the cursor to filter on only the databases that are not in SIMPLe recovery already

change the cursor definition to this:

declare c1 cursor for

select

name

from master.sys.databases

WHERE recovery_model_desc <> 'SIMPLE'

AND name not in ('master','model','msdb','tempdb')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

stevekgoodwin SSC Enthusiast Points: 116 More actions · Answer 4

Thanks. Came in very handy. I modified the version I used to explicitly shrink the log file. The dev server here has been running for years with full logging.

You'll need to uncomment the 3 exec statements for this to work. You don't want to run this one accidentally against production.

USE MASTER

declare

@isql varchar(2000),

@dbname varchar(64),

@logfile varchar(128)

declare c1 cursor for

SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size

FROM sys.master_files mf

inner join sys.databases d

on mf.database_id = d.database_id

where recovery_model_desc <> 'SIMPLE'

and d.name not in ('master','model','msdb','tempdb')

and mf.type_desc = 'LOG'

open c1

fetch next from c1 into @dbname, @logfile

While @@fetch_status <> -1

begin

select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

--exec(@isql)

select @isql='USE @dbname checkpoint'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

--exec(@isql)

select @isql='USE @dbname DBCC SHRINKFILE (@logfile, 1)'

select @isql = replace(@isql,'@logfile',@logfile)

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

--exec(@isql)

fetch next from c1 into @dbname, @logfile

end

close c1

deallocate c1

david.sentelle SSC Journeyman Points: 91 More actions · Answer 5

I don't know if this is due to SQL versions, but I did have to make 2 modifications. I've got tables with hyphens in the names. In order to correct this I had to put brackets around the @dbname variables where it is building the script.
The other change I had to make was the @dbname variable wasn't long enough for some of my DBs. I changed the declaration to varchar(128).
Here's the result.
USE MASTER declare @isql varchar(2000), @dbname varchar(128) declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb') open c1 fetch next from c1 into @dbname While @@fetch_status <> -1 begin select @isql = 'ALTER DATABASE [@dbname] SET AUTO_CLOSE OFF' select @isql = replace(@isql,'@dbname',@dbname) print @isql -- exec(@isql) select @isql = 'ALTER DATABASE [@dbname] SET RECOVERY SIMPLE' select @isql = replace(@isql,'@dbname',@dbname) print @isql -- exec(@isql) select @isql='USE [@dbname] checkpoint' select @isql = replace(@isql,'@dbname',@dbname) print @isql -- exec(@isql) fetch next from c1 into @dbname end close c1 deallocate c1
Just as in the original posting, uncommend the "exec" statements to make this really tear things up. Otherwise, you can copy the output, review it, and run it in a separate query editor window.