MS SQL Server - How do I Remove old backups from a file?

Author

How do I Remove old backups from a file?

Todd Chittenden

2005-10-28, 8:23 pm

I have read that it is better to use T-SQL scripts in Job to run backups
instead of creating a Maintenance Plan becuase you get better control.
However, I can't seem to get the correct results.

I am trying to setup a Job that backs up a Db to a backup file and have it
clean out old backup so the file does not grow out of control. I can't seem
to get the right combination of WITH clauses in my script:

BACKUP DATABASE [Db] TO [DeviceName]
WITH
NOINIT ,
RETAINDAYS = 4,
NAME = N'My Backup',
NOSKIP ,
STATS = 10,
NOFORMAT

The SQL Agent Service runs under the Windows Administrator account so it
shouldn't be a security issue.

I have tried varoius combinations of (NO)SKIP and (NO)INIT with no luck. My
backup devices just keep growing and never get purged.

Any suggestions?

Todd

Andrew J. Kelly

2005-10-28, 8:23 pm

Is this a tape or to disk? INIT is the option you want to use and will
definitely overwrite the old device on disk. But if it is less than 4 days
old it will not overwrite any of the file due to your RETAINDAYS setting.
You can not delete individual backups from a device. It is all or nothing.
If you need to keep 4 days around you should not use a Logical backup and
instead create a new filename each backup. Then have a job to delete the
old ones.

--
Andrew J. Kelly SQL MVP

"Todd Chittenden" < ToddChittenden@discu
ssions.microsoft.com> wrote in
message news:AD837D39-FFE0-4E7E-9F15- 5D3A538D606A@microso
ft.com...
>I have read that it is better to use T-SQL scripts in Job to run backups
> instead of creating a Maintenance Plan becuase you get better control.
> However, I can't seem to get the correct results.
>
> I am trying to setup a Job that backs up a Db to a backup file and have it
> clean out old backup so the file does not grow out of control. I can't
> seem
> to get the right combination of WITH clauses in my script:
>
> BACKUP DATABASE [Db] TO [DeviceName]
> WITH
> NOINIT ,
> RETAINDAYS = 4,
> NAME = N'My Backup',
> NOSKIP ,
> STATS = 10,
> NOFORMAT
>
> The SQL Agent Service runs under the Windows Administrator account so it
> shouldn't be a security issue.
>
> I have tried varoius combinations of (NO)SKIP and (NO)INIT with no luck.
> My
> backup devices just keep growing and never get purged.
>
> Any suggestions?
>
> Todd

Linchi Shea

2005-10-29, 3:23 am

> But if it is less than 4 days old it will not overwrite any of the
> file due to your RETAINDAYS setting.

In case you really want to overwrite the file regardless, you could use the
FORMAT option in the WITH clause.

Linchi

"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:Oi%23YNT$2FHA.3400@tk2msftngp13.phx.gbl...
> Is this a tape or to disk? INIT is the option you want to use and will
> definitely overwrite the old device on disk. But if it is less than 4
> days old it will not overwrite any of the file due to your RETAINDAYS
> setting. You can not delete individual backups from a device. It is all or
> nothing. If you need to keep 4 days around you should not use a Logical
> backup and instead create a new filename each backup. Then have a job to
> delete the old ones.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Todd Chittenden" < ToddChittenden@discu
ssions.microsoft.com> wrote in
> message news:AD837D39-FFE0-4E7E-9F15- 5D3A538D606A@microso
ft.com...
>
>

Todd Chittenden

2005-10-31, 8:23 pm

Thank you both for your input. Looks like the way to go is individual files
for each backup, instead of lumping them all into one device.

Andrew: You mentioned that I should then create a job to clean out the old
backup files. Can you give me a few pointers on how to script this task? Is
it T-SQL, CmdExe? some other type?

I have some experience with both T-SQL and VBA but am not a great expert in
either. I have an idea of the logic required: "Look in XYZ folder and if any
file is older than N days, delete it." Simple enough.

How do I translate into script?

Thanks in advance.

Todd

Todd Chittenden

2005-10-31, 8:23 pm

NEVER MIND. I think I got it using the xp_sqlmaint proc.

EXECUTE master.dbo.xp_sqlmaint N'-D DbName -BkUpMedia DISK -BkUpDB "E:\SQL
Backups\FolderName" -DelBkUps 4DAYS -BkExt "BAK"'

Of course, if you have any suggestions for refining, or other avenues to
pursue, I would love to hear them.

Thanks again!
--

Todd

Andrew J. Kelly

2005-10-31, 8:23 pm

Here are some tsql samples that you may find useful:

/*
This procedure deletes all files in the hardcoded dir that are
older than the datetime passed in.
*/
CREATE PROCEDURE delete_old_backup_fi
les
@DelDate DATETIME

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Delete VARCHAR(300), @Msg VARCHAR(100), @Return INT

-- Used later when we cast strings to Datetimes
SET DATEFORMAT MDY

-- Create a table to hold the results of the DIR command
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))

-- Create a table to hold any errors
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
DROP TABLE #Errors

CREATE TABLE #Errors (Results VARCHAR(1000))

-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec @Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'

SET @Error = @@ERROR

IF @Error <> 0 OR @Return <> 0
BEGIN
IF @Return = 1
SET @Error = -1
SET @Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END

-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2)
< '00' OR
SUBSTRING(FName,1,2)
> '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'

-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,4
0) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName
,1,20) AS DATETIME) < @DelDate
AND SUBSTRING(FName,40,4
0) LIKE '%.BAK'
ORDER BY CAST(SUBSTRING(FName
,1,20) AS DATETIME)

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname
WHILE (@@fetch_status = 0)
BEGIN

-- Delete the old backup files
SET @Delete = 'DEL "C:\Data\Backups' + @FName + '"'

INSERT INTO #Errors (Results)
exec @Return = master..xp_cmdshell @Delete

IF @@RowCount > 1 OR @Return = 1
BEGIN
SET @Error = -1
SET @Msg = 'Error while Deleting file ' + @FName
GOTO On_Error
END

PRINT 'Deleted ' + @FName + ' at ' +
CONVERT(VARCHAR(28),
GETDATE(),113)

FETCH NEXT FROM curDir INTO @Fname
END

CLOSE curDir
DEALLOCATE curDir

DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)

RETURN @Error
END
END
GO

-------------------------------------
/*
Deletes a single file if the date is old enough.
The file name including the full path is passed in to this sp.
*/

CREATE PROCEDURE [dbo].& #91;delete_single_ba
ckup_file]
@BackupFileName NVARCHAR(400)
,@DelDays INT -- Number of days past the current datetime before you can
delete them
,@Msg NVARCHAR(1000) OUT

AS

SET NOCOUNT ON

SET DEADLOCK_PRIORITY LOW

DECLARE @SQL NVARCHAR(1000), @FName NVARCHAR(200), @Error INT
DECLARE @Return INT, @DelDate DATETIME, @DirCmd NVARCHAR(800)
DECLARE @DelDir NVARCHAR(800), @TmpMsg NVARCHAR(1000), @Rows INT

SET @Msg = ''
SET @TmpMsg = ''

IF @DelDays = 0
BEGIN
SET @Error = -9
SET @Msg = 'The Retain Days value can not be 0 '
GOTO On_Error
END

IF OBJECT_ID('[tempdb].[dbo].[#dirlist]') IS NOT NULL
DROP TABLE #DirList

-- Create a table to hold the results of the DIR command
CREATE TABLE #dirlist ([FName] NVARCHAR(1000), [DS] VARCHAR(14),
[CreateDate] DATETIME)

-- Create a table to hold any errors
IF OBJECT_ID('[tempdb].[dbo].[#Errors]') IS NOT NULL
DROP TABLE #Errors

CREATE TABLE #Errors ([Results] NVARCHAR(1000))

-- Set the date the file must be older than before we can delete it.
SET @DelDate = DATEADD(dd,@DelDays * -1,GETDATE())

-- Get the dir that the files belong in.
SET @DelDir =
REPLACE(@BackupFileN
ame,RIGHT(@BackupFil
eName,CHARINDEX('',
REVERSE(@BackupFileN
ame),1)-1),'')

-- Insert the results of the dir cmd into a table so we can see if they
need to be deleted
SET @DirCmd = 'dir /OD /TC "' + @BackupFileName + '"'

INSERT INTO #dirlist ([FName])
exec @Return = master..xp_cmdshell @DirCmd

IF @Return = 1
BEGIN
IF EXISTS (SELECT * FROM #DirList WHERE [FName] LIKE 'The system cannot
find the%')
BEGIN
-- This means there was an invalid dir specified
SET @Msg = 'The system cannot find the file or path specified. "' +
@BackupFileName + '"'
SET @Error = -1
END
ELSE
IF EXISTS (SELECT * FROM #DirList WHERE [FName] LIKE 'File Not
Found%')
BEGIN
-- There simply weren't any files to delete so just return.
SET @Msg = 'There were no files matching this name: "' +
@BackupFileName + '"'
SET @Error = 0
END
ELSE
BEGIN
-- Trap for misc errors.
SET @Msg = 'There was an unspecified error while looking for:
"' + @BackupFileName + '"'
SET @Error = -8
END

GOTO On_Error
END

-- Remove the garbage
DELETE FROM #dirlist WHERE
[FName] IS NULL OR
[FName] LIKE '%<DIR>%' OR
ISDATE(SUBSTRING(FNa
me, 1, 10)) = 0

-- First strip out everything but the actual file name and then get just
the datetime portion.
-- Populate the CreateDate column with the datetime recreated from the file
name itself.
-- This way we never have to worry about the regional settings to mess with
the date strings.
UPDATE #DirList SET [FName] = SUBSTRING([FName], LEN([FName]) - PATINDEX('%
%',REVERSE([FName])) + 2, 200)
UPDATE #DirList SET [DS] = REVERSE(SUBSTRING(RE
VERSE([FName]),5,14))
UPDATE #DirList SET [CreateDate] = CONVERT(DATETIME,(LE
FT([DS],8) + ' ') +
(SUBSTRING([DS],9,2) + ':' + SUBSTRING([DS],11,2) + ':' +
SUBSTRING([DS],13,2)))

-- Remove any rows that have a CreateDate later than our del date so we only
Delete the ones we want to.
DELETE FROM #DirList WHERE [CreateDate] > @DelDate

-- Create a cursor and for each file name do the delete.
-- The files will be processed in date order, oldest first.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT [FName]
FROM #dirlist
ORDER BY [CreateDate]

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname
WHILE (@@fetch_status = 0)
BEGIN

TRUNCATE TABLE #Errors

-- Use the /F switch to force deletes of ReadOnly files and the /Q
switch to supress the wildcard notification.
SET @SQL = 'DEL /F /Q "' + @DelDir + @FName + '"'

-- execute the DEL command and place any results in a temp table
INSERT INTO #Errors ([Results])
EXEC @Return = master..xp_cmdshell @SQL

SET @Rows = @@ROWCOUNT

SELECT TOP 1 @TmpMsg = LEFT([Results],120) FROM #Errors WHERE [Results]
IS NOT NULL

-- If there was a severe error with the command it will return a 1
IF @Return = 1
BEGIN
-- This is most likely due to an invalid path or the folder being
moved.
SET @Msg = @Msg + (ISNULL(@TmpMsg,'') + ' The file "' + @DelDir +
@FName + '" could not be deleted.' + CHAR(13) + CHAR(10))
END

ELSE

BEGIN
IF @Rows > 1
BEGIN
SET @Error = -3
-- This may not be a big deal since the file may have already
been deleted.
IF @TmpMsg LIKE 'Could Not Find%'
SET @Msg = @Msg + (' The specified file(s) was not found: "'
+ @DelDir + @FName + '"' + CHAR(13) + CHAR(10))

ELSE
-- The file may be locked.
IF @TmpMsg LIKE 'The process cannot access the file%'
SET @Msg = @Msg + (' The file: "' + @DelDir + @FName + '"
could not be accessed. It is most likely locked by another process.' +
CHAR(13) + CHAR(10))
ELSE
-- Unknown error
SET @Msg = @Msg + (' File: "' + @DelDir + @FName + '" could
not be deleted.' + CHAR(13) + CHAR(10))

END
END

FETCH NEXT FROM curDir INTO @Fname
END

CLOSE curDir
DEALLOCATE curDir

-- If we got here everything must have went OK
RETURN 0

On_Error:
BEGIN
RETURN @Error
END
GO

--
Andrew J. Kelly SQL MVP

"Todd Chittenden" < ToddChittenden@discu
ssions.microsoft.com> wrote in
message news:B1FA1E2E-BA89-49C3-AA93- 44FEAFCEADCE@microso
ft.com...
> NEVER MIND. I think I got it using the xp_sqlmaint proc.
>
> EXECUTE master.dbo.xp_sqlmaint N'-D DbName -BkUpMedia DISK -BkUpDB
> "E:\SQL
> Backups\FolderName" -DelBkUps 4DAYS -BkExt "BAK"'
>
>
> Of course, if you have any suggestions for refining, or other avenues to
> pursue, I would love to hear them.
>
> Thanks again!
> --
>
> Todd
>
>

Sponsored Links