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.