1

I am trying to change the Recovery Mode from Full to Simple if the database doesn't have any Full or T.Log backups setup from past week days.

The problem I am facing with the below script is it's selecting database name twice and spitting the code to execute along with data files, butIi need only log files to shrink and change the database to Simple recovery mode.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--ALTER PROCEDURE [dbo].[usp_shrink_log]
--WITH Recompile
--AS
--BEGIN
declare
    @isql varchar(2000),
    @dbname varchar(64),
    @logfile varchar(128);

declare c1 cursor
for
SELECT distinct(D.[name]) AS [database_name]
    ,(Select BS3.Name where BS3.type='1') as Logfile
FROM 
    sys.databases D
LEFT JOIN  
    ( 
    SELECT BS.[database_name],  
    MAX(BS.[backup_finish_date]) AS [last_log_backup_date]  
    FROM msdb.dbo.backupset BS  
    WHERE BS.type = 'L'  
    GROUP BY BS.[database_name]  
    ) BS1  
ON D.[name] = BS1.[database_name] 
LEFT JOIN  
    ( 
    SELECT BS.[database_name],  
    MAX(BS.[backup_finish_date]) AS [last_data_backup_date]  
    FROM msdb.dbo.backupset BS  
    WHERE BS.type = 'D'  
    GROUP BY BS.[database_name]  
    ) BS2  
ON D.[name] = BS2.[database_name] 
LEFT JOIN 
    ( 
    SELECT NAME,database_id,type
    from sys.master_files MF
    ) BS3
ON D.Database_id=BS3.database_id
WHERE 
        D.[recovery_model_desc] <> 'SIMPLE'
    and D.[name] not in ('master', 'model', 'msdb', 'tempdb',
        'ReportServer', 'ReportServerTempDB')
    AND BS1.[last_log_backup_date] IS NULL
    OR  BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
    and BS3.type='1'
ORDER BY D.[name];

open c1;
fetch next from c1 into @dbname, @logfile;

While @@fetch_status <> -1
begin
    select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'

    print @isql
    --exec(@isql)

    select @isql='USE ' + @dbname + ' checkpoint'

    print @isql
    --exec(@isql)

    select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
    print @isql
    --exec(@isql)

    fetch next from c1 into @dbname, @logfile
end

close c1;
deallocate c1;
--END

closed as off-topic by Erik Darling, Tara Kizer, John aka hot2use, Kin Shah, Shawn Melton Nov 30 '17 at 15:39

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, Tara Kizer, John aka hot2use, Kin Shah, Shawn Melton
If this question can be reworded to fit the rules in the help center, please edit the question.

0

Here is a much-cleaned-up version without the WHILE cursor.

Do not run this until you check with the business, to make sure they are happy changing over to SIMPLE Recovery Mode!

USE [master];
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

--ALTER PROCEDURE [dbo].[usp_shrink_log]
--WITH Recompile
--AS
--BEGIN
    DECLARE @isql VARCHAR(8000);
    SET @isql = '';

    SELECT @isql = @isql + 'ALTER DATABASE ' + QUOTENAME(D.[name]) + ' SET RECOVERY SIMPLE;
USE ' + QUOTENAME(D.[name]) + ';
CHECKPOINT;
DBCC SHRINKFILE (' + QUOTENAME(BS3.[name]) + ', 1);
'
    --SELECT D.[name], BS3.[name]
    FROM (
        SELECT *
        FROM sys.databases
        WHERE [recovery_model_desc] <> 'SIMPLE'
          AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb',
            'ReportServer', 'ReportServerTempDB')
        ) D
    LEFT JOIN (
        SELECT BS.[database_name]
            ,MAX(BS.[backup_finish_date]) AS [last_log_backup_date]
        FROM msdb.dbo.backupset BS
        WHERE BS.[type] = 'L' -- Log Backups
        GROUP BY BS.[database_name]
        ) BS1 ON D.[name] = BS1.[database_name]
    LEFT JOIN (
        SELECT BS.[database_name]
            ,MAX(BS.[backup_finish_date]) AS [last_data_backup_date]
        FROM msdb.dbo.backupset BS
        WHERE BS.[type] = 'D' -- Differential Backups
        GROUP BY BS.[database_name]
        ) BS2 ON D.[name] = BS2.[database_name]
    LEFT JOIN sys.master_files BS3 ON D.Database_id = BS3.database_id
        AND [type] = '1' -- LOG
    WHERE (
            BS1.[last_log_backup_date] IS NULL
            OR
            BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
            )
    ORDER BY D.[name];

    PRINT(@isql);
    --EXEC(@isql);
--END

It outputs one of these per row:

ALTER DATABASE [My lovely DB] SET RECOVERY SIMPLE;
USE [My lovely DB];
CHECKPOINT;
DBCC SHRINKFILE ([My lovely Log File], 1);
  • i get the magic of coding :) – JayM Nov 30 '17 at 15:35
0

I fixed my own code :) how ironic.

Here is the piece need to change (around line 43)

SELECT NAME,database_id,type
from  sys.master_files MF
Where type = 1 -- I added this bit!

Hope this code helps some one here.

  • or line 47 change to AND (BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date] )and BS3.type='1' – SqlWorldWide Nov 30 '17 at 14:32
  • 1
    Also be careful with this type of thing. You don't want to be changing a database to simple recovery without business buy in. Instead you should be reporting on databases without backups correctly scheduled and then have the business make a decision as to full/simple recovery and then schedule backups/change to simple based on that decision. FYI this includes development databases. In that case the "business" is the development team using it. – Kenneth Fisher Nov 30 '17 at 14:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.