為所有使用者創建的數據庫設置簡單恢復模式並縮小日誌文件

我希望你能指出我正確的方向。我不是 T-SQL 的常用使用者，但我做了一些Google搜尋，並找到了下面的腳本。我稍微更正了腳本。

我希望腳本：

1. 選擇所有數據庫，系統數據庫除外。
2. 將恢復設置為簡單。
3. 收縮每個 db(.ldf) 的日誌文件，除了系統 db

劇本：

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
   begin
   select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
   select @isql = replace(@isql,'@dbname',@dbname)
   print @isql
   exec(@isql)
   select @isql='USE @dbname checkpoint'
   select @isql = replace(@isql,'@dbname',@dbname)
   print @isql
   exec(@isql)
   select @isql='DBCC SHRINKFILE @dbname.ldf'
   select @isql = replace(@isql,'@dbname',@dbname)
   print @isql
   exec(@isql)

   fetch next from c1 into @dbname
   end
close c1
deallocate c1

使用腳本收縮除系統數據庫之外的所有數據庫的日誌文件。

USE MASTER   
GO    
SET QUOTED_IDENTIFIER ON  
GO  
SET ARITHABORT ON  
GO  

DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)  

DECLARE DatabaseList CURSOR   
FOR   
SELECT name,recovery_model_desc  
FROM sys.databases  
WHERE state_desc = 'ONLINE'  
AND is_read_only = 0  
and database_id>4  
ORDER BY name  

OPEN DatabaseList  
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc  
WHILE @@FETCH_STATUS = 0     
BEGIN   

SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')  

If @DBRecoveryDesc='Full'  
Begin  
    Print('Use ['+@DBName+'] 
           GO  

          ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
          GO   

           DBCC SHRINKFILE ('''+@LogicalFileName+''',10)  
           GO  

           ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH  NO_WAIT
           GO ')  
Print '----------------------------------------------------------- '  
END  

If @DBRecoveryDesc='Simple'  
Begin   
    Print('Use ['+@DBName+']  
           GO  

           DBCC SHRINKFILE ('''+@LogicalFileName+''',10)    
           GO    
')  
Print '----------------------------------------------------------- '

END

        FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
     END  
CLOSE DatabaseList  
DEALLOCATE DatabaseList

引用自：https://dba.stackexchange.com/questions/68991