SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use a SELECT to ALTER properties of all databases of certain type...


Use a SELECT to ALTER properties of all databases of certain type...

Author
Message
jasona.work
jasona.work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10375 Visits: 12598
So I was flipping the DBs on one of my QA servers from FULL to SIMPLE recovery (we don't need PiT recovery in QA) and got thinking...

Yes, that explains the fire trucks you heard go by a few minutes ago smart guy...
Hehe

Anyways.
Is it possible to write and ALTER statement something like this:
ALTER DATABASE (SELECT NAME FROM sys.databases 
WHERE recovery_model_desc = N'SIMPLE'
AND name NOT IN ('master','model','msdb','tempdb'))
SET RECOVERY SIMPLE WITH NO_WAIT;



Yes, I know this could be a potentially dangerous trick (just replace 'set recovery' with 'set offline' for some *real* fun) but when you've got a bunch of DBs to switch, it's quicker than copy/paste, and easier to key up then using sp_msforeachdb (which as someone pointed out on SQLSkills I believe, doesn't always work right)

Regardless, I've already done the copy-paste method, but for future reference...

Thanks,
Jason
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100383 Visits: 33014
As far as I know you can only do something like through ad hoc mechanisms or programming (PowerShell to the rescue).

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50825 Visits: 10844
Keeping in mind that you'll be running it from a script yourself and not exposing it to the internet, how about something like this?

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE'
AND name NOT IN ('master','model','msdb','tempdb')
ORDER BY name;




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
whenriksen
whenriksen
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1281 Visits: 640
Put the dynamic sql into a variable and Execute the variable.

DECLARE @SQL varchar(max) = '';
SELECT @SQL = @SQL + '
ALTER DATABASE ' + name + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE'
AND name NOT IN ('master','model','msdb','tempdb')
ORDER BY name;

Execute (@SQL);



Also, your query shows you are setting your database to simple recovery if they are already in simple recovery mode. Is that what you want? Should that be
where NOT recovery_model_desc = 'SIMPLE'



Wes
(A solid design is always preferable to a creative workaround)
jasona.work
jasona.work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10375 Visits: 12598
D'OH!

The
WHERE recovery_model_desc = 'SIMPLE'

should've been = 'FULL'

Thanks.

I hadn't thought of the dynamic SQL idea, although I'm now messing with that to try to check what DBs SQL Logins might have access to before dropping them...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search