6

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.
  • A while loop is a cursor. I'm not sure where the mindset that a while loop is faster than a cursor comes from; I'm sure there are cases where this is true, when the while loop is firehose and the cursor is over-engineered, but in most cases if you've coded them correctly they are identical. – Aaron Bertrand May 1 '16 at 15:54
  • 2
    And yes, you're being paranoid about performance. Any maintenance you need to do against every database has to be done one by one, it doesn't matter how you get there - while loop, cursor, what have you. – Aaron Bertrand May 1 '16 at 15:55
13

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!

| improve this answer | |
5

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);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.