Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use a SELECT to ALTER properties of all databases of certain type... Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 6:27 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
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...


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
Post #1507091
Posted Tuesday, October 22, 2013 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1507100
Posted Tuesday, October 22, 2013 9:33 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:48 PM
Points: 4,053, Visits: 3,490
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
Post #1507221
Posted Tuesday, October 22, 2013 9:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
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'

Post #1507224
Posted Tuesday, October 22, 2013 10:40 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 728, Visits: 5,231
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...
Post #1507239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse