Delete old Backup files using T-sql script
-
You can add the sp_configure to the script to enable and disable the xp_cmdshell and then you are set.
-
I run my whole backup and DR scripts with SSIS and so far I have little problems with it. It makes the maintenance of the scripts a lot easier that TSQL scripts.
-
Salom Rangel,
Yes u r right.Its better to turn off xp_cmdshell after using.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
Without site of the code or a screenshot of the maintenance plan, we are all just guessing as to why the files are not being deleted. Perhaps the poster can upload something ...
We all seem to be going round the houses just to delete a few files ... The sample code below can be run as a cmd file from a schedule task and contain multiple rows for different file types and/or locations.
Forfiles.exe is installed by default on 2003 or available in the Windows 2000 Resource Kit.
@echo offForfiles /p Y:\Backups\Database /s /m *.bak /d -2 /c "Cmd /C del /Q @file"
C:\>forfiles /?
FORFILES [/P pathname] [/M searchmask] [/S]
[/C command] [/D [+ | -] {dd/MM/yyyy | dd}]
Description:
Selects a file (or set of files) and executes a
command on that file. This is helpful for batch jobs.
Parameter List:
/P pathname Indicates the path to start searching.
The default folder is the current working
directory (.).
/M searchmask Searches files according to a searchmask.
The default searchmask is '*' .
/S Instructs forfiles to recurse into
subdirectories. Like "DIR /S".
/C command Indicates the command to execute for each file.
Command strings should be wrapped in double
quotes.
The default command is "cmd /c echo @file".
The following variables can be used in the
command string:
@file - returns the name of the file.
@fname - returns the file name without
extension.
@ext - returns only the extension of the
file.
@path - returns the full path of the file.
@relpath - returns the relative path of the
file.
@isdir - returns "TRUE" if a file type is
a directory, and "FALSE" for files.
@fsize - returns the size of the file in
bytes.
@fdate - returns the last modified date of the
file.
@ftime - returns the last modified time of the
file.
To include special characters in the command
line, use the hexadecimal code for the character
in 0xHH format (ex. 0x09 for tab). Internal
CMD.exe commands should be preceded with
"cmd /c".
/D date Selects files with a last modified date greater
than or equal to (+), or less than or equal to
(-), the specified date using the
"dd/MM/yyyy" format; or selects files with a
last modified date greater than or equal to (+)
the current date plus "dd" days, or less than or
equal to (-) the current date minus "dd" days. A
valid "dd" number of days can be any number in
the range of 0 - 32768.
"+" is taken as default sign if not specified.
/? Displays this help message.
Examples:
FORFILES /?
FORFILES
FORFILES /P C:\WINDOWS /S /M DNS*.*
FORFILES /S /M *.txt /C "cmd /c type @file | more"
FORFILES /P C:\ /S /M *.bat
FORFILES /D -30 /M *.exe
/C "cmd /c echo @path 0x09 was changed 30 days ago"
FORFILES /D 01/01/2001
/C "cmd /c echo @fname is new since Jan 1st 2001"
FORFILES /D +19/11/2009 /C "cmd /c echo @fname is new today"
FORFILES /M *.exe /D +1
FORFILES /S /M *.doc /C "cmd /c echo @fsize"
FORFILES /M *.txt /C "cmd /c if @isdir==FALSE notepad.exe @file"
C:\>
-
Mark,
Thanks for the tip. I usually use a vbscript with the filesystem object to handle these types of tasks. I never knew of the ForFiles command-line utility. I checked it out at: http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx . Because it is still officially supported, using this in a job as a command-line task would be would be better than using the undocumented xp_delete_file T-SQL extended stored procedure. I also avoid using xp_cmdshell for obvious security reasons.
Brandon Forest
-
Prueba este código debe servirte, para lo que preguntas.
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [master, model, msdb] TO DISK = ''C:\BKSQL\<AUTO>.sqb'' WITH COMPRESSION = 2, FILECOUNT = 2, ERASEFILES_ATSTART = 6,"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END
-
Here's how I do it with a sp. The path can be local or remote. Here days = 3, all "bak" files in the directory older that 3 days will be deleted.
You can run the commands as a sp, or can fold into a bat file and run on the server with a bat file and schedule with windows scheduler
John.
ALTER procedure [dbo].[jc_deleteBackup]
as
-- need to set the path and the number of days
declare @cmd varchar(2000)
set @cmd = 'forfiles /p c:\sqlbackups /d -3 -S -m *.bak -C "cmd /c del @file"'
exec master..xp_cmdshell @cmd
-
it's true is kind of rubbish because it can't handle multiple paths. The Maintenance plan executes a command similar to the one below:
xp_delete_file 0,'+@backuppath+',N''bak'','+@DeleteDate+',1
@backuppath speaks for itself
@DeleteDate is the threshold date beyond which anything is deleted expressed by
convert(varchar(50), CAST(getdate() AS datetime),126)
-
Hi, You can try the below link for SQL Server 2000.
http://www.mssqltips.com/tip.asp?tip=1324
SQL Server 2005 have different script to delete the backup file as the below. Save this script as a .sql and call the file from batch file. The batch files are given below.
=========================
DECLARE @DeleteFiles NVARCHAR(MAX)
DECLARE DeleteFile CURSOR
FOR
SELECT 'exec xp_cmdshell ''DEL "'
+ physical_device_name + '"''' +CHAR(13)+CHAR(10)
FROM msdb.dbo.backupmediafamily ms
JOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id
WHERE backup_finish_date < GETDATE() And backup_finish_date > GETDATE()-2 AND
Type ='D'
order by backup_finish_date desc
OPEN DeleteFile
FETCH NEXT FROM DeleteFile
INTO @DeleteFiles
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@DeleteFiles)
print(@DeleteFiles)
FETCH NEXT FROM DeleteFile
INTO @DeleteFiles
END
CLOSE DeleteFile
DEALLOCATE DeleteFile
======================
Batch file.
-- Change it 2 HOSTNAME words for the actual name of the MSSQL Instance name.
-- Check the below paths are correct for the server and change if needed.
sqlcmd -S <instancename> -d msdb -E -i d:\mssql\dba\sql\delete_backups.sql -o d:\mssql\dba\reports\delete_backup_report.txt
=================
call the batch file from the schedule task.
-
I set up a clean up task through the maintenance plan in SQL 2005 standard version.
When I execute it keeps on failing with below error,
Message
Executed as user: COMP\DBSServiceDX1. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:31:24 PM Could not create DTS.Application because of error 0x80070005
It seems to me that I can't create a cleanup task through the Maintenance plan.
-
I set up a clean up task through the maintenance plan in SQL 2005 standard version.
When I execute it keeps on failing with below error,
Message
Executed as user: COMP\DBSServiceDX1. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:31:24 PM Could not create DTS.Application because of error 0x80070005
It seems to me that I can't create a cleanup task through the Maintenance plan.
-
Please check the maintenance plan.
Edit the maintennace cleanup task and see the folder u r tryin to delete it from.
is it correct ,also check hte extension and
select the option delete files older than the follwing.....specify days...
After doing this, then also if it doesnt clean back up files, then
delete the maintenance plan and create it again....
It helped me...
Regards
Sushant Kumar
MCTS,MCP -
Hi,
Here is a small piece of code which suits ur requirement. Kindly Go through and let me know if you need any thing else.
SET NOCOUNT ON
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
PRINT 'Running reconfigure'
RECONFIGURE
GO
DECLARE @rc INT
DECLARE @cmd VARCHAR(1000)
DECLARE @cursor CURSOR
DECLARE @physicaldevicename VARCHAR(1000)
DECLARE @Err VARCHAR(1000)
SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a
inner join msdb..backupmediafamily as b on a.media_set_id = b.media_set_id
WHERE a.backup_start_date
< GETDATE()-5 ORDER BY a.backup_start_date,a.database_name
OPEN @cursor
WHILE 1=1
BEGIN
FETCH FROM @cursor INTO @physicaldevicename
IF @@fetch_status <> 0
BEGIN
SET @Err = @@fetch_status
PRINT @Err
BREAK
END
ELSE
BEGIN
SET @cmd = 'del ' + @physicaldevicename
PRINT 'Starting deleting the backup file ' + @cmd
EXEC @rc = master.dbo.xp_cmdshell @cmd
IF @rc <> 0
BEGIN
PRINT 'Backup File ' + @physicaldevicename + ' was not deleted'
END
ELSE
BEGIN
PRINT 'Backup File ' + @physicaldevicename + ' deleted successfully at timestamp '+ CONVERT(VARCHAR,GETDATE(),109)
END
END
END
CLOSE @cursor
DEALLOCATE @cursor
GO
sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
PRINT 'Running reconfigure'
RECONFIGURE
GO
SET NOCOUNT OFF
Thanks & Regards,
Manjunath C Bhat.
You can aslo subscribe to my blog. 🙂
Thanks & Regards,
Manjunath C Bhat,
http://manjunathcbhat.blogspot.com,
http://manjunathcbhat.wordpress.com -
@Manjunath
Hi i am working on same script. can you let me know how i can provide the network share location to delete old t-log backup file in your script?
Also i want to delete file 7 days older than so how i can set that in your script??
otherwise its working fine .
thanks for nice posting
-
logicinside22 (1/17/2012)
@ManjunathHi i am working on same script. can you let me know how i can provide the network share location to delete old t-log backup file in your script?
Also i want to delete file 7 days older than so how i can set that in your script??
otherwise its working fine .
thanks for nice posting
This code segment is what you will modify for the date
WHERE a.backup_start_date
< GETDATE()-5
It pulls the logfile location from the database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply