Script to fix database for all online databases.

Set database owner = 'sa'

Set RECOVERY = Simple & Shrink databases

 

print '-- SQL Script to fix owner and Recouvery mode'
print ''
SET NOCOUNT ON;
Declare @sql nvarchar(max);
declare @dbname nvarchar(255);
BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db
FROM sys.databases f
WHERE f.state_desc = 'ONLINE'
    AND (f.database_id > 4)


while ((select count(*) from #db)>0)
BEGIN
    
    
    Set @sql = '';
    SELECT top 1
    @dbname = name,
     @sql =
    
     -- change db owner to SA
     + 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

     -- SET RECOVERY SIMPLE
     + 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)
     +'USE [MASTER]; '+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) END
    FROM #db f
    ORDER BY f.name;

    if NOT(@sql = '')
    SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;

    print @sql;
    --exec (@sql);

    delete from #db where name = @dbname;

END

Create script to change owner

PRINT '---------------------------';
PRINT '-- SQL Script to fix owner to "sa"';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''?'' ))
BEGIN
    PRINT ''---- Fix owner user to "sa"''
    USE [?];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to fix owner to "sa"'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END

Sql Script to set recovery mode full.

PRINT '---------------------------';
PRINT '-- SQL Script to Set Recovery Mode FULL';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''?'' ))
BEGIN
    PRINT ''---- Fix recovery mode full''
    USE Master;ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to Set Recovery Mode FULL'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END