February 15, 2010 at 8:56 pm
Hi All,
I have a problem with my maintainence plan. Normally it has to delete the previous day backup and keep todays backup in the specified folder.
The backup is done successfully but the previous day backup is not being deleted.
Don't know exactly why it is getting locked.I also tried to delete the previous day backup to ensure the space availabilty just before 10 mins the backup would kick off.
It is showing an error saying it is being used by some other process...
At windows i ave tried to see any open sessions,open files are accessing this folder or file.Assuming that some process archving this prev day backup. But it is not so.
But i guess , sql agent itself is locking the entire folder during the backup operation. What do you say???
Finally, i thought writing one more job which would kick off before half an hour just before the backup is taken.
so, i need a batch file which will retrieve all the backup files which is less than getdate() and keeps the most recent one i..e yesterday's backup and delete the remaining so that atleast i can have the latest backup i.e prev day backup even if my today's backup fails.
i.e assume i have a folder D:\BACKUPDUMP. Here, daily backups will be taken at night 9:30 pm. The backups files are as follows (assume)
D:\BACKUPDUMP
|-mosis_db_201002110935.BAK
|-mosis_db_201002120940.BAK
|-mosis_db_201002130932.BAK
|-mosis_db_201002140941.BAK
|-mosis_db_201002150933.BAK
Now suppose today's date is 2010-02-16 and the backup is expected to taken at 9:30 pm.What i need is before the backup can happen, i need a job/query which i can schedule it which will
delete all the old backups from the folder with an exception of retaining the previous day's backup. That means, the job must delete all backups except
|-mosis_db_201002150933.BAK.
How can i accomplish this????
Thanks in advance.
February 15, 2010 at 9:55 pm
Was this running successful before?
EnjoY!
February 18, 2010 at 2:06 am
yes. But now it is failing...
That to it is working fine in some days but failing in some day's n the days are also are not specific days.
That's is problem to figure out.
February 18, 2010 at 4:36 am
try this dos command, u can have it scheduled...
FORFILES /P <filepath> /S /M *.BAK /D -1 /C "cmd /c del @file"
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 19, 2010 at 11:26 pm
Hi,
I myself i tried to do some coding and come up with a stored procedure which would delete the previous backups.
Am putting this for the benefit for all the sql server central team.
CREATE PROCEDURE sp_delete_old_backups
AS
BEGIN
CREATE TABLE #Backupfiles
(
fname varchar(100),
removed_dt datetime
)
INSERT #Backupfiles(fname)
EXEC xp_cmdshell 'dir D:\BackupDumps\*.bak /b'
-- select * from #Backupfiles
delete from #Backupfiles where fname is null and removed_dt is null
UPDATE #Backupfiles
SET removed_dt = convert(datetime,SUBSTRING(fname,15,8),101) --This step is dependent on backupfilename Example : MyApp_db_201002142130.bak
DECLARE @backup_file VARCHAR(1000)
DECLARE @cmd VARCHAR(1000)
-- check if today's backup is avaiable or not.If it is not avaiable , do not delete all the old backups.
-- Atleast u shud live with the prev's day's backup.
IF ( SELECT COUNT(*) FROM #Backupfiles
WHERE convert(varchar(10),removed_dt,101) = CONVERT(varchar(10),getdate(),101)
) > 0 -- MEAN'S it is existing. The count will be 1 which is > 0
BEGIN
DECLARE del_cur CURSOR FOR -- declare the cursor
SELECT fname FROM #Backupfiles
where convert(varchar(10),removed_dt,101) <> convert(varchar(10),GETDATE(),101)
OPEN del_cur --open the cursor
FETCH NEXT FROM del_cur INTO @backup_file -- fetch the filename and store it in a varaible
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @backup_file
SET @cmd = 'exec xp_cmdshell ''DEL D:\BackupDumps\'+@backup_file+''''
PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM del_cur INTO @backup_file
END
CLOSE del_cur
DEALLOCATE del_cur
END
ELSE
-- KEEP the previous day's backup and delete the remaining all backups
-- Special case , even if u have a
BEGIN
DECLARE del_cur CURSOR FOR -- declare the cursor
SELECT fname FROM #Backupfiles
where convert(varchar(10),removed_dt,101) < convert(varchar(10),GETDATE()-1,101)
OPEN del_cur --open the cursor
FETCH NEXT FROM del_cur INTO @backup_file -- fetch the filename and store it in a varaible
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @backup_file
SET @cmd = 'exec xp_cmdshell ''DEL D:\BackupDumps\'+@backup_file+''''
PRINT @cmd
EXEC (@cmd) --DEL cmd executed
FETCH NEXT FROM del_cur INTO @backup_file
END
CLOSE del_cur
DEALLOCATE del_cur
END
DROP TABLE #Backupfiles
END
Thank You.
February 20, 2010 at 1:09 am
appreciate your efforts..u should better try the dos script..it is fast and simple...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 22, 2010 at 6:57 am
I don't know if this will help, but I use this scheduled vbs file to copy my sql backups to a remote backup server and delete old backups.
dim objFSO,objfolder, fold, f, files, file
Set objFSO = CreateObject("Scripting.FileSystemObject")
'----------------clean up backup server
set objfolder = objFSO.GetFolder("\\dellnxcluster2\L$\SQLBackupsoc-wan-01-sql")
for each fold in objfolder.SubFolders
for each f in fold.files
if f.DateLastModified < date()-5 thenf.delete
next
next
'-----------------clean up local sql server
set objfolder = objFSO.GetFolder("d:\SQL\MSSQL.1\MSSQL\Backup")
for each fold in objfolder.SubFolders
for each f in fold.files
if f.DateLastModified < date()-5 thenf.delete
next
next
'-----------------copy backup sql files to backup server
set objfolder = objFSO.GetFolder("d:\SQL\MSSQL.1\MSSQL\Backup")
for each fold in objfolder.SubFolders
for each f in fold.files
if objfso.FileExists("\\dellnxcluster2\L$\SQLBackupsoc-wan-01-sql\" & fold.name & "\" & f.name) = False then
f.copy "\\dellnxcluster2\L$\SQLBackupsoc-wan-01-sql\" & fold.name & "\" & f.name
end if
next
next
set file = nothing
set files = nothing
set f = nothing
set fold = nothing
set objfolder = nothing
set objFSO=nothing
February 22, 2010 at 9:21 am
As long as we are providing our file delete scripts here's mine. Note: this does not clean up the backup histories in the database which should also be in a clean up job. It also assumes that .bak and .trn extensions are used.
USE [master]
GO
DECLARE @tDatabases TABLE
(
databaseId int
--, DATABASE_NAME varchar(25)
)
INSERT INTO @tDatabases
(
databaseId
)
SELECT DISTINCT
s_mf.database_id
from
sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND s_mf.database_id <> 2 --eliminate temp db
DECLARE @dbId int;
Declare @DateToDelete datetime;
declare @DateString char(8);
DECLARE @filePath varchar(max);
SET @DateToDelete=DateAdd(day,-1,GetDate());
set @DateString = Convert(nvarchar(30),@DateToDelete,1)+' '+ Convert(nvarchar(30),@DateToDelete,8);
--Set up SQL string
SET @filePath = '\\[Insert Backup Path Here]\'
DECLARE cursor_usage CURSOR FOR
SELECT
databaseId
FROM @tDatabases;
OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @execString nvarchar(max)
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''bak'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''trn'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);
FETCH NEXT FROM cursor_usage INTO @dbId;
END;
CLOSE cursor_usage;
DEALLOCATE cursor_usage;
February 23, 2010 at 7:51 am
can't you simply add a maintenance cleanup task to your maintenance plan that executes the backup?
all you need to do is specify the folder, file extension, and the age of the files you want to cleanup
February 23, 2010 at 8:36 am
I think it is safe to say based on all of the scripts here we have all found maintenance pplans flaky and have developed methods to get around them.
Alan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply