SQL Server – Are Cursors/While Loops the Only Way to Make Administrative Changes to Multiple Databases?

alter-databaseperformancepowershellsmosql server

I specifically want a programmable way to change the recovery model of all the databases on a server, and i know this solution can be applied to all 'Alter Database' commands. While I know SMO in PowerShell can solve this problem very easily:

Import-Module SQLPS
CD SQL\*ServerName*\*InstanceName*\databases
foreach ($database in (ls -force)){
$database.recoverymodel = 'Full'
$database.update
}

I'm looking for the most efficient T-SQL solution.
It has been ingrained in my head to avoid cursors at all costs, however I can't think of a set based solution to perform alter statements.
Here is the cursor based solution.

DECLARE @sql varchar(MAX), @name varchar(50)

DECLARE cur CURSOR FOR
SELECT name 
FROM sys.databases
where name <> 'tempdb';
OPEN cur

FETCH NEXT FROM cur 
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER DATABASE ' + @name + ' SET RECOVERY FULL;'
    exec(@sql)
    FETCH NEXT FROM cur INTO @name
END
CLOSE CUR
DEALLOCATE cur
  1. This can also be done with a while loop, but that solution still goes through a looping process… do while loops offer significantly better performance?
  2. Am i being paranoid about performance? I say that because administrative tasks such as this aren't done very often, and there generally isn't a massive amount of objects you need to loop through. However i always think "What if i worked in a large environment with thousands of objects" and then i begin to feel guilty that I may not be using the most efficient solution.

Best Answer

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:

SQLCMD mode in SSMS

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!

Related Question