May 18, 2006 at 8:09 am
Because we recently got a new SQL Backup utility, I am doing a lot of my db backups using TSQL instead of Maintenance Plans.
Does anyone know a way to easily delete backup files older than a week with T-SQL?
The maint. plans do it by adding a switch to the xq_sqlmaint procedure that get called, but I can't get that procedure to run when all I ask it to do is delete old backups.
I'd like to do something like:
exec xp_sqlmaint '-D dktemp -DelBkUps "5days"'
But when I do that it gives me:
(28 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Any suggestions?
Thanks,
Jason
The Redneck DBA
May 18, 2006 at 12:49 pm
Try this...
CREATE
PROCEDURE dbo.usp_dropOldBackups
(
@prmLife INT = 0 -- number of days after which device gets dropped
)
AS
BEGIN
/*
2006.06.18 ATL - this code ripped by author from usp_dbMaint8 for public domain use.
*/
SET NOCOUNT ON
DECLARE
@bdName sysname, -- backup device name
@errNum INT, -- for @@ERROR when applicable
@expDate DATETIME -- backup device expiration date
IF @prmLife > 0
BEGIN
-- backup device list
DECLARE @bdNames TABLE
(
bdName sysname NOT NULL
)
SET @expDate = DATEADD(day, -@prmLife, GETDATE()) -- set 'expiration' date
-- get a list of all the devices that have expired.
INSERT INTO @bdNames
SELECT sd.name
FROM master..sysdevices sd
INNER JOIN msdb..backupmediafamily bmf ON sd.name = bmf.logical_device_name
INNER JOIN msdb..backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE
sd.status & 16 = 16 AND
sd.cntrltype = 2 AND
bs.backup_finish_date < @expDate
SELECT TOP 1 @bdName = bdName
FROM @bdNames
ORDER BY bdName
WHILE @@ROWCOUNT > 0
BEGIN
EXEC @errNum = sp_dropdevice @logicalname = @bdName, @delfile = 'delfile'
IF @errNum = 0
BEGIN
SELECT 'Complete - Backup Device (' + @bdName + ') was successfully dropped'
END
ELSE
BEGIN
SELECT 'ERROR - Backup Device (' + @bdName + ') drop failed'
END
-- ENDIF @errNum = 0
DELETE FROM @bdNames
WHERE bdName = @bdName
SELECT TOP 1 @bdName = bdName
FROM @bdNames
ORDER BY bdName
END
-- ENDWHILE @@ROWCOUNT > 0
END
--ENDIF @prmLife <> 0
RETURN
END
GO
----------------------------
<= as with anything; no warrantees written or implied...=> but I have been using a more elaborate version of the above for many years. One thing to be careful of if you don't have the older backups on tape or elsewhere: remember that you must restore starting with a FULL or FILEGROUP backup(s).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply