Wednesday 18 November 2015

Remove Backup History for databases no longer on the server

USE [msdb]
;
DECLARE @DBName NVARCHAR(128)
DECLARE @Delete nvarchar(max)

CREATE TABLE #DatabaseList
    (
    databasename nvarchar(128),
    deletestatus bit
    );
   
INSERT INTO #DatabaseList
SELECT DISTINCT database_name,
                0
FROM msdb.dbo.backupset
WHERE database_name NOT IN(
    SELECT name
    FROM sys.databases)
   
WHILE (SELECT COUNT(*) FROM #databaseList WHERE deleteStatus = 0) > 0
    BEGIN

        SELECT TOP 1 @DBName = databasename
        FROM #databaseList
        WHERE DeleteStatus = 0;

            SET @Delete = 'EXEC [msdb].[dbo].[sp_delete_database_backuphistory] @database_name = N''' +@DBName + ''''
   
            EXEC (@Delete)
   
            IF @@ERROR<>0 PRINT @Delete
   
        UPDATE #databaseList
        SET deletestatus = 1
        WHERE databaseName = @DBName;

    END

DROP TABLE #databaseList;

No comments:

Post a Comment

Note: only a member of this blog may post a comment.