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 |
|
sql_wedge
Starting Member
18 Posts |
 Posted - 2005-09-09 : 05:27:16
|
| I am having problems with a backup plan in Sql Server.In the plan I say that files older than 2 days must be deleted.At the moment there is 14G free space on the drive with 2 days worth of backups.What happens is the backup plan executes but the total daily backup amount exceeds 14G, the job then fails and then does not delete files older than 2 days.Is it possible to delete the older files before the backup plan executes either through SQL SERVER or Windows? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-09 : 06:44:08
|
| One way might be to use xp_cmdshell to make a DIR listing, parse the filenames and dates from that, and then issue DEL commands for the individual files (using xp_cmdshell again)Kristen |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-09-09 : 09:38:47
|
quote:
Originally posted by Kristen
One way might be to use xp_cmdshell to make a DIR listing, parse the filenames and dates from that, and then issue DEL commands for the individual files (using xp_cmdshell again)Kristen
Use "DIR *.bak /b" That gives you all filenames with a .bak extention and returns only the filenames. Then there is no need to parse the results. Only a need to validate the proper ones to delete prior to the backup.A word of caution on deleting your backups prior to backing up the DB: If your DB has some sort of problem that causes the backup to fail and then your DB crashes... you will not be a happy camper because your backup is no longer there.  DanielSQL Server DBAwww.dallasteam.com*Edit - by "DB crashes" I mean a user randomly deletes 50,000 records from the database or something else that may require a restore. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-09 : 09:47:47
|
| "Only a need to validate the proper ones to delete prior to the backup"How do you know what is more than 2 days old without the DIR File date?I suppose you could JOIN to something in MSDB ... (might not be there any more)Or maybe include the Date/TimeStamp in the filename ... (bit risky that)Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-09-09 : 10:16:09
|
quote:
Originally posted by Kristen
How do you know what is more than 2 days old without the DIR File date?
Oh. I guess I need to start reading the full post rather than the subject line only.  But on that note, you should be able to get the files that are older than 2 days from the filename (DBBackup20050909.BAK). That should be easy enough and accurate.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-09 : 11:03:15
|
| I am just imagining restoring an old file from tape. Its taken me hours to find, and get the IT boys to restore it.There she is, you beauty!Right ... lets get it restored.Darn it, where's it gone? Ha! SQL Scheduled Task has deleted it already!I suppose sensible people would restore it to a different folder. I wonder if our erstwhile IT department are capable of following the particular instruction to restore it to a different folder .... Kristen |
|
|
|
|
|
|
Deleteing files before backup