Script to Fix Database for All Online Databases.

 1
 2print '-- SQL Script to fix owner and Recouvery mode'
 3print ''
 4SET NOCOUNT ON;
 5Declare @sql nvarchar(max);
 6declare @dbname nvarchar(255);
 7BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
 8SELECT * INTO #db
 9FROM sys.databases f
10WHERE f.state_desc = 'ONLINE'
11    AND (f.database_id > 4)
12
13while ((select count(*) from #db)>0)
14BEGIN
15
16    Set @sql = '';
17    SELECT top 1
18    @dbname = name,
19     @sql =
20
21     -- change db owner to SA
22     + case when suser_sname(f.owner_sid) = 'sa' THEN '' ELSE 'USE ['+ f.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10)  END
23
24     -- SET RECOVERY SIMPLE
25     + CASE when f.recovery_model_desc = 'SIMPLE' THEN ''  ELSE +'USE [MASTER]; '+ 'ALTER DATABASE ['+ f.name+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+ CHAR(13) + CHAR(10)
26     +'USE [MASTER]; '+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) END
27    FROM #db f
28    ORDER BY f.name;
29
30    if NOT(@sql = '')
31    SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;
32
33    print @sql;
34    --exec (@sql);
35
36    delete from #db where name = @dbname;
37
38END

 1
 2PRINT '---------------------------';
 3PRINT '-- SQL Script to fix owner to "sa"';
 4PRINT '-- '+CAST(getdate() as nvarchar(100));
 5PRINT '---------------------------';
 6SET NOCOUNT ON;
 7DECLARE @sql nvarchar(MAX);
 8DECLARE @dbname nvarchar(255);
 9BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
10SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
11AND (f.database_id > 4)
12
13DECLARE @sqltemplate nvarchar(MAX);
14SET @sqltemplate = '
15PRINT ''-- Database "?" '';
16IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''?'' ))
17BEGIN
18    PRINT ''---- Fix owner user to "sa"''
19    USE [?];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
20END;
21'
22PRINT 'PRINT ''----------------------'';';
23PRINT 'PRINT ''-- Script to fix owner to "sa"'';';
24PRINT 'PRINT ''----------------------'';';
25WHILE ((SELECT count(*) FROM #db)>0) BEGIN
26SET @sql = '';
27SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
28DELETE FROM #db WHERE name = @dbname;
29PRINT @sql;
30END

 1
 2PRINT '---------------------------';
 3PRINT '-- SQL Script to Set Recovery Mode FULL';
 4PRINT '-- '+CAST(getdate() as nvarchar(100));
 5PRINT '---------------------------';
 6SET NOCOUNT ON;
 7DECLARE @sql nvarchar(MAX);
 8DECLARE @dbname nvarchar(255);
 9BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
10SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
11AND (f.database_id > 4)
12
13DECLARE @sqltemplate nvarchar(MAX);
14SET @sqltemplate = '
15PRINT ''-- Database "?" '';
16IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''?'' ))
17BEGIN
18    PRINT ''---- Fix recovery mode full''
19    USE Master;ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;;
20END;
21'
22PRINT 'PRINT ''----------------------'';';
23PRINT 'PRINT ''-- Script to Set Recovery Mode FULL'';';
24PRINT 'PRINT ''----------------------'';';
25WHILE ((SELECT count(*) FROM #db)>0) BEGIN
26SET @sql = '';
27SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
28DELETE FROM #db WHERE name = @dbname;
29PRINT @sql;
30END