Andrew Fraser DBA

Windows PowerShell run script on all SQL Servers recovery mode

Micrsoft SQL Server databases should be kept in full recovery mode (the equivalent of Oracle’s archivelog mode) except for the master, tempdb, ReportServerTempDB databases.

To check all databases in a SQL Server instance, run the below script in a SQL command window. Optionally uncomment the EXEC line to have this script switch databases to full recovery mode.

use [master]

set nocount on

DECLARE @name sysname
      , @sqlcmd VARCHAR(MAX)

DECLARE db_cur CURSOR FOR
 SELECT d.name
   FROM sys.databases d
  WHERE d.recovery_model = 3 -- simple recovery 
    AND d.name != 'master' 
    AND d.name NOT LIKE '%tempdb'  -- tempdb , ReportServerTempDB, etc.

OPEN db_cur
FETCH NEXT FROM db_cur INTO @name

WHILE @@fetch_status = 0
BEGIN
    SET @sqlcmd = 'alter database ' + QUOTENAME ( @name ) + ' set recovery full with no_wait ; '
    --EXEC ( @sqlcmd )
    PRINT @sqlcmd
    FETCH NEXT FROM db_cur INTO @name
END

CLOSE db_cur
DEALLOCATE db_cur
GO

Alternatively sp_MSforeachdb could do the same with fewer lines of code, except sp_MSforeachdb is undocumented and buggy.

Using Windows Powershell it is possible to automate that same script to run against many SQL Servers.

(1) First create a text file named e.g. C:\ServerInstances.txt with one line for each SQL Server instance, case insensitive. The port and instance name have to be specified if they are not the default values (1433 and default)

myserver1.mydomain.com
myserver2.mydomain.com,32001
myserver3.mydomain.com\instanceB
myserver4.mydomain.com\instanceC,32001

(2) Then copy the below Windows PowerShell script to a file named e.g. ServerInstances.ps1

Import-Module sqlps -DisableNameChecking

foreach ( $ServerInstance in get-content C:\ServerInstances.txt )
{
Write-Host "Working on $ServerInstance ..."

Invoke-Sqlcmd -Server "$ServerInstance" -Database master -Verbose -Query "
set nocount on

DECLARE @name sysname
      , @sqlcmd VARCHAR(MAX)

DECLARE db_cur CURSOR FOR
 SELECT d.name
   FROM sys.databases d
  WHERE d.recovery_model = 3 -- simple recovery 
    AND d.name != 'master' 
    AND d.name NOT LIKE '%tempdb'  -- tempdb , ReportServerTempDB, etc.

OPEN db_cur
FETCH NEXT FROM db_cur INTO @name

WHILE @@fetch_status = 0
BEGIN
    SET @sqlcmd = 'alter database ' + QUOTENAME ( @name ) + ' set recovery full with no_wait ; '
    --EXEC ( @sqlcmd )
    PRINT @sqlcmd
    FETCH NEXT FROM db_cur INTO @name
END

CLOSE db_cur
DEALLOCATE db_cur
GO"
}

Again comment out the EXEC line to have the script change the recovery mode.

(3) Run that script in a Windows PowerShell command window.

PS> .\ServerInstances.ps1

or if you get error “File .\ServerInstances.ps1 cannot be loaded because the execution of scripts is disabled on this system.”, then:

PS> powershell.exe -executionpolicy bypass -file .\ServerInstances.ps1

Again comment out the EXEC line if you want the change to be applied rather than just reported on.

That uses AD authentication by default, SQL Server authentication could be used instead by adding the -Username and -Password options.

If setting databases into full recovery mode, remember to make sure transaction log backups are scheduled, otherwise the transaction logs will grow without limit.

Other T-SQL scripts you need can be plugged in in place of of recovery mode checks. If doing this a lot it may be worth setting up and using a Central Management Server instead of Windows PowerShell.

H/t Moshin and dmc.