Are cursors/While loops the only way to make administrative changes to multiple databases?
As far as loops go for this type of thing, don't worry about it. Loops and cursors have a bad reputation because there are normally better set-based approaches that are often faster. For admin stuff, sometimes loops are the only way, and no set-based ways of doing this spring to mind, although you can parallelise tasks with tools like start in DOS, SSIS, Powershell etc.
Having said that, I prefer to use SQLCMD mode for this type of thing. It's a special mode you can switch on in SQL Server Management Studio (SSMS) via the main menu > Query > SQLCMD Mode ... like this:
Once you have switched this mode on, you have access to all kinds of commands like :connect
to connect to another server, :r
to read a file, :out
to redirect output, SQLCMD variables, and any DOS commands by prefixing them with two exclamation marks, eg !!dir
.
For your example, you could do something like this:
:connect .\sql2014
SET NOCOUNT ON
GO
-- Redirect output back to normal
:out d:\temp\temp.sql
GO
SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL;'
FROM sys.databases
WHERE recovery_model_desc != 'FULL'
AND database_id > 4
AND name Not In ( 'distribution', 'SSISDB' )
GO
PRINT 'GO'
GO
-- Redirect output back to normal
:out STDOUT
-- Optionally read/run the temp file you have scripted
--:r d:\temp\temp.sql
GO
This allows you to "script a script", then you can review it, change if required and there is an audit trail of what has been run. Give SQLCMD mode a try!
Here's one method that doesn't require a looping construct:
DECLARE @sql nvarchar(MAX) = N'';
SELECT
@sql += N'ALTER DATABASE '
+ QUOTENAME(name)
+ N' SET RECOVERY FULL;
'
FROM sys.databases
WHERE database_id > 4
AND name NOT IN ( N'distribution', N'SSISDB' );
PRINT @sql;
--EXEC(@sql);
I agree with wBob's answer in that one need not be overly concerned with performance with admin scripts that are run infrequently.
It is not guaranteed that the string concatenation method above will always produce the expected result for a statement that affects multiple rows according to this Connect item because the behavior is plan dependent.
Other methods besides the cursor include CLR and XML. Below is an example of the XML method, which is more reliable for non-trivial plans and provides guaranteed ordering when ORDER BY
is needed.
DECLARE @sql varchar(MAX) = N'';
SET @sql = (
SELECT N'ALTER DATABASE '
+ QUOTENAME(name)
+ N' SET RECOVERY FULL;
'
FROM sys.databases
WHERE
database_id > 4
AND name NOT IN ( N'distribution', N'SSISDB' )
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(MAX)');
PRINT @sql;
--EXEC(@sql);