Attention All Script Guru need a hand here
-
My goal is to delete backup files that is not needed in the recovery of our database. I modified 1 script that will delete backup files older than the latest backup (that can be run in version 7). For example we have backups dated sept. 4-10 when running this script it will delete all backup files dated sept. 9 below and will retain the latest backup sept. 10. So let say if we schedule this script to run at 10pm of sept 11 assuming the folder with backup files only have backups dated sept 10 the script will not delete any backup files since the only backup in the folder is the latest backup then we schedule the database maintenance backup at 11pm what would be the result?..... your right the folder will be filled up with backup files dated sept. 11 and the total will be folder = sept. 10-11 backup files since the script is executed 10pm and database maintenance plan is set to 11pm it will not affect the 11pm backups correct?? the script is ok in my dummy database sql version 2000 but when scheduled the script in sql version 7 at the next day the folder contains only backup files of sept 11 (refer to the BOLD statement for the flow of the script 
need script gurus here for correction if the script is not right. Im just a fresh graduate and currently trying to adapt at sql scripting thanks...."-=Still Learning=-"
Lester Policarpio
-
BTW here is the script i used (stored procedure) than can be run as a job schedule :
CREATE PROCEDURE usp_DeleteOldBackup
AS
DECLARE @path varchar(1024)
DECLARE @extension varchar(3)
DECLARE @pathension varchar(1000)
DECLARE @DeleteBeforeDate datetime
DECLARE @FName varchar(1024)
DECLARE @delete varchar(1024)
DECLARE @error varchar(1000)
DECLARE @msg varchar(1000)
DECLARE @count int
-- Drop tables if they exist --
IF OBJECT_ID('dbo.cmdshell') IS NOT NULL
DROP TABLE cmdshell
IF OBJECT_ID('dbo.Errors') IS NOT NULL
DROP TABLE Errors
CREATE TABLE cmdshell (Fentry varchar(1024),FDate VARCHAR(25), FName VARCHAR(50))
CREATE TABLE Errors (Results VARCHAR(1000))
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SET @path = 'C:\lester\'--change path of backup files
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SET @extension = 'BAK'
SET @pathension = 'dir /OD '+@Path+'*.'+@Extension
insert into cmdshell(fentry) exec master..xp_cmdshell @pathension
delete from cmdshell where fentry not like '%.BAK%'
delete from cmdshell where fentry is NULL
UPDATE cmdshell set fdate = substring(fentry,1,10) , fname = substring(fentry,40,50)
-- Set @DeleteBeforeDate --
IF @DeleteBeforeDate is null
select @DeleteBeforeDate = (Select top 1 FDate
from cmdshell
where right(FName,3) = @Extension
order by FDate DESC)
--delete record for most recent backup
delete from cmdshell where FDate >= @DeleteBeforeDate
SET @count = 0
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT FName
FROM cmdshell
WHERE FDate < @DeleteBeforeDate
OPEN curDir
FETCH NEXT FROM curDir INTO @FName
WHILE (@@fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @Delete = 'DEL "'+ @Path + @FName + '"'
INSERT INTO Errors (Results)
exec master..xp_cmdshell @Delete
IF @@RowCount > 1
BEGIN
SET @Error = -1
SET @Msg = 'Error while Deleting file ' + @FName
GOTO On_Error
END
-- PRINT @Delete
PRINT 'Deleted ' + @FName + ' at ' + CONVERT(VARCHAR(28),GETDATE(),113)
set @count = @count+1
FETCH NEXT FROM curDir INTO @FName
END
print ''
print 'The total number of Backups deleted is: ' print @count
CLOSE curDir
DEALLOCATE curDir
DROP TABLE cmdshell
DROP TABLE Errors
On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO
"-=Still Learning=-"
Lester Policarpio
-
Lester, have you had a look through the script library on this site. There is a good chance that a script like this has already been written.
Also, just a tip, set things like your path as parameters. Then you don't need to change the script to choose a different path, you just supply a different value for the parameter.
eg: exec usp_DeleteOldBackup @path = 'c:\lester\', or exec usp_DeleteOldBackup @path = 'c:\backups\'
--------------------
Colt 45 - the original point and click interface
-
Thanks for the correction Phil, yes actually the script originated in the script library but when i ran it errors occured (in sql version 7.0) so i made a modification just to make it compatible to version 7.0. It runs well but the output is not what i've expected like when i tested it in version 2000
"-=Still Learning=-"
Lester Policarpio
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply