I had created a stored procedure which will delete the old database backup file based on certain parameter. The parameters are:
1. Backup type as want to delete full, differential or transaction log backups. \
2. From days
3. End day
You had to enable XP_CMDSHELL in database instance. This is required to enable the procedure to delete database backup. Below is the query to enable it.
The procedure to delete old backup files is under:
Example
1. Backup type as want to delete full, differential or transaction log backups. \
2. From days
3. End day
You had to enable XP_CMDSHELL in database instance. This is required to enable the procedure to delete database backup. Below is the query to enable it.
USE MASTER
GO
EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO
The procedure to delete old backup files is under:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteOldBackupFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON
DECLARE @IsFileExists INT
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)
DECLARE OldFiles CURSOR FAST_FORWARD FOR
SELECT
bmf.physical_device_name
from msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2
AND TYPE = @BackupType
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday
OPEN OldFiles
FETCH NEXT FROM OldFiles INTO @OldFiles
WHILE @@FETCH_STATUS =0
BEGIN
EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN
PRINT 'Deleted File : ' + @DeletedFile
SET @DeletedFile = 'DEL ' + @OldFiles
EXEC XP_CMDSHELL @DeletedFile
END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END
EXEC DeleteDBBackupOldFiles
@BackupType = 'D',
@StartDayFromToday = 7,
@EndDayFromToday = 10
No comments:
Post a Comment