April 14, 2010 at 4:01 am
Hi
Any one with a script that can delete backups.bk files that are older than 3days, pls help.
April 14, 2010 at 4:13 am
something along the lines of
declare @deldate datetime
set @deldate = cast(dateadd(day, -3 , getdate()) as nvarchar(20))
EXECUTE master.dbo.xp_delete_file 0,N'DRIVELEETER:\PATH',N'BAK',@DelDate, 1
should help
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 14, 2010 at 4:30 am
What if you want to run the script every day as a job that must run after business hrs Instead of inserting a date value?
April 14, 2010 at 4:44 am
THE-FHA (4/14/2010)
What if you want to run the script every day as a job that must run after business hrs Instead of inserting a date value?
The query that Perry gave will delete files older than 3 days. you can create a job and include the query as a job step..
April 14, 2010 at 4:52 am
I am having challenge in testing this script, im running sql2005 on win 2003 standard eddition.
April 14, 2010 at 5:24 am
And if the backup files that needs to be deleted are more than one and all of them are named using the instance_name and date, how will you go about deleting multiple files.
April 14, 2010 at 5:41 am
THE-FHA (4/14/2010)
And if the backup files that needs to be deleted are more than one and all of them are named using the instance_name and date, how will you go about deleting multiple files.
declare @deldate datetime
set @deldate = cast(dateadd(day, -3 , getdate()) as nvarchar(20))
EXECUTE master.dbo.xp_delete_file 0,N'D:\BackupFolder',N'BAK',@DelDate, 1
I assume your backup files are stored in d:\BackupFolder. Running this code will delete ALL files which are 3 or more days old.
Where r u getting stuck?
April 14, 2010 at 5:46 am
EXECUTE master.dbo.xp_delete_file 0,N'D:\BackupFolder',N'BAK',@DelDate, 1
LET'S SAY I WANT TO DELETE TODAYS BACKUP just to test if you script works.
send me your code.
One more thing do i put the backup and with its extention 'xxxx.bk` or just the backupname?
April 15, 2010 at 8:30 am
THE-FHA (4/14/2010)
And if the backup files that needs to be deleted are more than one and all of them are named using the instance_name and date, how will you go about deleting multiple files.
Dude, the end parameter '1' indicates whether to delete files from subfolders. So,
EXECUTE master.dbo.xp_delete_file 0,N'X:\Backups',N'BAK',@DelDate, 1
will delete all BAK files older than 3 days in the X:\Backups folder and subfolders underneath. Try it out on your test server and you'll see how it works.
If you're unsure just run a Windows backup of the location where the backup files are stored before using the script
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2010 at 12:51 am
This script is not working.
I even tested it by downloading northwind database from ms website and perfomed backup on it on D:\Data\.
please tel me how you would go aboput executing the script if you are deleting today`s backup?
April 16, 2010 at 12:58 am
I guess creating the maintenance plan with maintenance cleaup task would help you in a better way rather than writing a script and scheduling the Job
Abhijit - http://abhijitmore.wordpress.com
April 16, 2010 at 4:15 am
no it wont work its looking for files older than 3 days. To delete files for a backup you just created change the day to minutes or seconds like so
set @deldate = cast(dateadd(mi, -3 , getdate()) as nvarchar(20))
or
set @deldate = cast(dateadd(ss, -3 , getdate()) as nvarchar(20))
this will delete everything older than 3 minutes or 3 secs, make a copy of the directory first.
What version\build number of SQL Server are you using
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2010 at 7:53 am
Wow...
April 16, 2010 at 1:58 pm
Perry's way will work if you set it up correctly. using the maintenance plan clean up task will also work. (generally what I use as its pretty easy.)
But just for the heck of it I wrote this now becuase I thought it would be kinda fun: (it should also work :-P, or at least hopefully)
P.S. deleting files when u are unsure how to setup basic code is probably not such a good idea then. Dont mean to be harsh, but spend a bit of time actually looking at the code before you state that "the script does not work."
Anyway, Good luck, and have fun exploring databases! 😉
CREATE PROC dbo.DeleteOldBackups
/*
Author:Nicholas Williams
Date:16th April 2010
Desc:Removes backup files.
Notes:
The @iNoDays parameter defines the amount of days which must have passed before the backup is deleted. if it is desired that backups older than 2 days be removed, then this must be set to the value of -2. if no value is supplied it will default to -3 (days)
The @vcDatabaseName parameter defines the specific database upon which it is desired that the backups are removed. if this has no value, then the procedure will generate (but not execute) the code required to delete all backups older than the iNoDays is set to.
The @cTypeOfBackup parameter defines the type of backup to be deleted. L for T-Log, I for differential and D for full backups. by default this will be set to Full backups.
The @iMaxHistory paramter defines the maximum amount of history to search for old backups. by default this is set for -30, or to ignore backups that are older than 30 days. this is simply there to minimize the amount of data scanned. if there are older backups present for which there is a need to remove, then this can be customized otherwise it should be left ato its default setting.
This procedure requires xp_cmdshell to work.
it can be enabled by running the following code:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
this procedure will need to be execute by someone with sysadmin access to work properly.
usage:
To remove Full backups older than one day for the AdventureWorks database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = 'AdventureWorks', @iNoDays = -1
To remove Full backups older than five days for the AdventureWorks database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = 'AdventureWorks', @iNoDays = -5
To generate code for the removal of Full backups older than 3 days for all databases:
EXEC dbo.DeleteOldBackups
To remove Full backups older than 3 days for the AdventureWorks Database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = 'AdventureWorks'
To Remove T-Log backups older than 3 days for the AdventureWorks Database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = 'AdventureWorks', @cTypeOfBackup = 'L'
To Get help:
EXEC dbo.DeleteOldBackups @cHelp = '?'
*/
@iNoDaysINT= NULL
,@vcDatabaseNameVARCHAR(100)= NULL
,@cTypeOfBackupCHAR(1)= NULL
,@iMaxHistoryINT= NULL
,@cHelpCHAR= NULL
AS
SET NOCOUNT ON
DECLARE
@vcNameVARCHAR(1500)
IF @cTypeOfBackup NOT IN ('L','D','I') AND @cTypeOfBackup IS NOT NULL
BEGIN
PRINT 'Inncorrect use of @cTypeOfBackup procedure. value must be either ''L'' (T-Log), ''D'' (Full Backup, or ''I'' Differential. in the absence of a value, full backups will be used.'
RETURN
END
IF @cHelp = '?'
BEGIN
GOTO Help
END
IF @vcDatabaseName IS NULL
BEGIN
GOTO AllDatabases
END
/*This section will only remove backups for a specific database*/
DECLARE Backup_cursor CURSOR 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 bs.database_name = @vcDatabaseName
AND bs.[type] = (ISNULL(@cTypeOfBackup,'D'))
AND bs.backup_start_date <= (SELECT DATEADD(d,(ISNULL(@iNoDays, -3)),GETDATE()))
AND bs.backup_start_date > (SELECT DATEADD(d,ISNULL(@iMaxHistory,-30), GETDATE()))
OPEN Backup_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
FETCH NEXT FROM Backup_cursor
INTO @vcName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('EXEC master.dbo.xp_cmdshell ''DEL '''+@vcName+''' /Q''')
PRINT 'The Following file(s) have been deleted: '''+ISNULL(@vcName,' ')+''''
FETCH NEXT FROM Backup_cursor
INTO @vcName
END
CLOSE Backup_cursor
DEALLOCATE Backup_cursor
SET NOCOUNT OFF
RETURN
/*Help Section*/
Help:
PRINT '
Notes:
The @iNoDays parameter defines the amount of days which must have passed before the backup is deleted. if it is desired that backups older than 2 days be removed, then this must be set to the value of -2. if no value is supplied it will default to -3 (days)
The @vcDatabaseName parameter defines the specific database upon which it is desired that the backups are removed. if this has no value, then the procedure will generate (but not execute) the code required to delete all backups older than the iNoDays is set to.
The @cTypeOfBackup parameter defines the type of backup to be deleted. L for T-Log, I for differential and D for full backups. by default this will be set to Full backups.
The @iMaxHistory paramter defines the maximum amount of history to search for old backups. by default this is set for -30, or to ignore backups that are older than 30 days. this is simply there to minimize the amount of data scanned. if there are older backups present for which there is a need to remove, then this can be customized otherwise it should be left ato its default setting.
usage:
To remove Full backups older than one day for the AdventureWorks database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = ''AdventureWorks'', @iNoDays = -1
To remove Full backups older than five days for the AdventureWorks database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = ''AdventureWorks'', @iNoDays = -5
To generate code for the removal of Full backups older than 3 days for all databases:
EXEC dbo.DeleteOldBackups
To remove Full backups older than 3 days for the AdventureWorks Database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = ''AdventureWorks''
To Remove T-Log backups older than 3 days for the AdventureWorks Database:
EXEC dbo.DeleteOldBackups @vcDatabaseName = ''AdventureWorks'', @cTypeOfBackup = ''L''
'
/*This section will remove generate the code to remove backups for all databases older than the specified date*/
AllDatabases:
DECLARE Backup_cursor CURSOR 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 bs.[type] = (ISNULL(@cTypeOfBackup,'D'))
AND bs.backup_start_date <= (SELECT DATEADD(d,(ISNULL(@iNoDays, -3)),GETDATE()))
AND bs.backup_start_date > (SELECT DATEADD(d,ISNULL(@iMaxHistory,-30), GETDATE()))
OPEN Backup_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
FETCH NEXT FROM Backup_cursor
INTO @vcName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT('EXEC master.dbo.xp_cmdshell ''DEL "'+@vcName+'" /Q''')
--PRINT 'The Following file(s) have been deleted: '''+ISNULL(@vcName,' ')+''''
FETCH NEXT FROM Backup_cursor
INTO @vcName
END
CLOSE Backup_cursor
DEALLOCATE Backup_cursor
SET NOCOUNT OFF
GO
April 16, 2010 at 2:00 pm
omg, it messed up all my nice formatting of white space!!! noooooo! *cries in corner* 😛
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply