TSQL : Development / UAT Server Prep

A simple script to loop all databases, setting them to SIMPLE recovery mode and SHRINKing any logs.

Very useful for restoring UAT / DEV databases from live environments.

sp_msforeachdb @command1 = '
 USE [?];
 IF DB_NAME() <> ''tempdb''
 BEGIN
  PRINT ''---''
  PRINT DB_NAME()
  PRINT ''---''
  DECLARE @databasename VARCHAR(1000)
  SET @databasename = DB_NAME()
  
  DECLARE @sqlcmd NVARCHAR(1000)
  SET @sqlcmd = ''ALTER DATABASE ['' + @databasename+ ''] SET RECOVERY SIMPLE ''
  EXECUTE (@sqlcmd)
  
  DECLARE @logfilename VARCHAR(1000)
  SELECT @logfilename = RTRIM(name) from sysfiles where fileid = 2
  SELECT @logfilename
  DBCC SHRINKFILE (@logfilename , 0, TRUNCATEONLY)
 END '