script to delete transactional log backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

script to delete transactional log backups

Author

Topic

putane.sanjay
Yak Posting Veteran

77 Posts

Posted - 2008-09-10 : 09:39:30

Hi,

Please send a script to delete transactional log backups older than 3 days in MS sql server 2000 on DR server.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-10 : 09:45:01

http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-10 : 12:58:48

Actually, I used this on the DR servers in the past:


CREATE PROC [dbo].[isp_Backup_Delete]
AS
SET NOCOUNT ON

DECLARE @now datetime, @rowCnt int, @bkpRetention int, @i int
DECLARE @bkpLocation varchar(512), @cmd nvarchar(4000), @file varchar(256)

SELECT @now = GETDATE(), @i = 1

SELECT IDENTITY(int, 1, 1) AS dbInfoID, BackupRetention, BackupLocation
INTO #dbInfo
FROM DatabaseInfo
WHERE ServerName = @@SERVERNAME
ORDER BY DatabaseName

SET @i = @@ROWCOUNT

CREATE TABLE #Dir(DirInfo VARCHAR(7000))

WHILE @i <> 0
BEGIN
	SELECT @bkpRetention = BackupRetention, @bkpLocation = BackupLocation
	FROM #dbInfo
	WHERE dbInfoID = @i

	SET @cmd = 'dir ' + @bkpLocation + ' /OD'

	IF RIGHT(@bkpLocation, 1) <> '\'
		SET @bkpLocation = @bkpLocation + '\'

	INSERT INTO #Dir
	EXEC master.dbo.xp_cmdshell @cmd

	DELETE 
	FROM #Dir
	WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @bkpRetention

	SELECT TOP 1 @file = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
	FROM #Dir

	SET @rowCnt = @@ROWCOUNT

	WHILE @rowCnt <> 0
	BEGIN
		SET @cmd = 'del ' + @bkpLocation + @file + ' /Q /F'

		EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
			
		DELETE
		FROM #Dir
		WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @file

		SELECT TOP 1 @file = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
		FROM #Dir
		
		SET @RowCnt = @@ROWCOUNT
	END

	DELETE FROM #Dir

	SET @i = @i - 1
END
GO

But it relies on a table that contains information about the databases at the DR site:


CREATE TABLE [dbo].[DatabaseInfo](
	[ServerName] [sysname] NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[BackupLocation] [varchar](512) NOT NULL,
	[BackupRetention] [int] NOT NULL CONSTRAINT [def_BackupFileRetention]  DEFAULT ((2)),
	[BackupLocationDestination] [varchar](512) NOT NULL,
 CONSTRAINT [PK_DatabaseInfo] PRIMARY KEY CLUSTERED 
(
	[ServerName] ASC,
	[DatabaseName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]