Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-02-15 : 22:55:54
|
| 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.BAKNow 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 willdelete 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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 01:44:57
|
| Quick and dirty approach:Delete all files in OldFiles FolderMove all backup files to OldFiles folder (they will be deleted by tomorrow's job)We extend this for files we want to keep for, say, 7 days:Remove Folder "Archive7"Rename Folder "Archive6" to "Archive7"...Rename Folder "Archive1" to "Archive2"Create new folder "Archive1"Move files to folder "Archive1"that way we don;t have to worry about file dates etc. However, it is important that the process only runs once a day! |
 |
|
|
|
|
|
job to delete old backups