Hello,
I am creating a Maintenance Plan in SQL Server 2005. The 'Back Up Database Task' has the choice 'Create a sub-directory for each database' as SQL Server 2000 does.
But when I add a 'Maintenance Cleanup Task' I do not see a choice to delete files on subdirectories. Looks like it only deletes the files from the directory I specify. So, looks like SQL Server 2005 is removing some functionality already available in SQL Server 2000. Is there a way to delete those files without needing to specify each directory?
I do not see any documentarion about xp_delete_file either.
Thanks,
Ben NevarezI am having the same exact problem. Does anybody have the answer?
Scott Suddarth|||
I have the same problem, I guess that you still did not find the solution?.
If ever,tell me..
FKy
THX
|||Same problem as well - I also can't find any info on xp_delete_file. Any progress?|||I have the same problem and found a blog in http://blogs.msdn.com/dditweb/archive/2005/10/18/482478.aspx : "I talked to the SQL team and the response was that it might be a feature added at a later time.".
Additionally, there is a problem with the date format on non-English servers.
I think, SQL Server 2005 should provide a way to delete old backup files in the same simple and robust way as SQL Server 2000.
|||We are taking this bug seriously and evaluating to get this addressed soon.
The suggested workaround in the blog is recommended, till this one gets addressed.
Thanks,
Gops Dwarak
|||I would like to hear more feedback on how reasonable is the suggested workaround in the blog. Please share your experience.
Thanks,
Gops Dwarak
|||The workarounds aren't reasonable. If I go the route of having a cleanup task in a given maintenance plan for each database that I have backing up to its own subfolder, then when I add a database to the system, I have to remember to go add a cleanup task for the new database -- on a server with hundreds of databases and a rather dynamic environment for adding/removing such, that is a nightmare.
This is the single-most obvious headache that I've encountered since upgrading a production SQL Server 2000 database system with just today. While this particular db system is in production, I have a lot of leeway with messing around with it for the next couple of weeks. For my other production SQL 2000 dbs that we'll be upgrading eventually, this issue causes real heartburn, and I'm none-too-happy about the workarounds.
Thanks for listening!
Edit: Well, given the option of putting all of my database backup files in one folder (i.e. for each database), I will say that is an easy workaround. I guess I wouldn't mind it on a server with just a few dbs. But, again, with a server with hundreds of SQL Server databases being backed up, this is non-ideal.
I'd also like to point out that when I upgraded to 2005, I was less than impressed with the migration of my SQL Server 2000 maint plans into 2005 -- they were so screwed up that I ended up just deleting them and recreating new ones within 2005 -- my guess is that lots of people will end up going this route.
|||My workaround is to use the free GNU UnxUtils (see http://unxutils.sourceforge.net ). I wrote an one-line batch file named delete_old_db_backups.bat to delete all files older than 9 days in the backup directory and all its subdirectories: "C:\scripts\find.exe C:\backup -name *backup* -mtime +9 -exec C:\scripts\rm.exe \"{}\" ;". This batch file is executed by a SQL Agent job, which I defined as the first task of type "Execute SQL Server Agent Job Task" in my maintenance plan. This solution now works fine.
Best regards
Walter
|||Hi
This workaround is not reasonable. I tried this but no of the BAK files are deleted. I tried to find information about the xp_delete_file procedure , but no chance. Probably there also a problem with the datesettings in my system. OS and SQL server are english, but the standards and format are German (Switzerland)
I'll prefere the same functionality to delete old files like in SQL2000
best reagards
Cougar J
|||I will echo the sentiments above that this is a major headache. Before reading this thread I had already added tasks to my maintenance plans to delete old backup files from each subdirectory, but that is a real hassle considering one of the primary benefits of maintenance plans is that they are easy to set up.
On a second note, I am apparently experiencing permission problems when my maintenance plan executes xp_Delete_File. Does it run under a separate security context? In this particular maintenance plan, my first task backs up several databases (to subdirectories), and the Maintenance Cleanup Tasks are unable to find the directory specified. There is no reason that it should have problems finding the path to the files from one task to the next.
I actually just got so frustrated debugging the maintenance plan that I wrote a VBscript command to delete the files and created a separate job to run it. It's ironic, because I had avoided maintenance plans for years in Sql2000 and I had decided to give them a shot in Sql2005. At the moment, it is easier for me to go back to my old maintenance scripts.
|||Thanks for all your feedback , this issue is getting addressed for Service Pack 1.
The cleanup task will have an option to clean up files in subfolders, this option will not be on by default and users can turn on to have the same functionality as SQL2000
Gops Dwarak
|||
Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?
Thanks again,
Kevin
Kmartin wrote:
Great news that it is being addressed. Now for te next logical question... When should we expect to see SP1? Q3, Q4, sometime in 2007?
Thanks again,
Kevin
Seconding Kmartin's question - any idea when we might see SP1?
Many thanks,
Megan
|||I am having a similar issue but I have daily backup files that have the extension of .nb7. When I create a maintenance cleanup task, to delete all nb7 files that are 2 days and older, SQL will not delete these files.
Further, I've created a test Maintenance task with a maintenance cleanup task specifically to delete the .nb7 files that are 1 day or older .When I execute the maintenance task, the files are still present in this directory. I've verified that the SQLServer2005MSSQLUsers$Servername$VVSQL user has full permissions to the directory root directory which is d:\backup\. I've also tried executing this maintenance task with the Everyone Group having full control over the d:\backup\ directory and still no dice. I've tried to change the file extension from .nb7 to nb7 (in the file extension spot) and the files still do not delete.
** I've changed the date between all of these steps and the .nb7 files are older than 1 month.
** I've checked the SQL server agent service credentials and they are set to LocalSystem, and I've verified that the SYSTEM user account has full control over the d:\backup\ directory.
** I've verified that the .nb7 files are NOT in use while this backup is Maintenance Task is being executed.
** I've even tried to trick ol' SQL by renaming the file extensions from .nb7 to .bak. SQL still didn't delete the file.
** The maintenance plan, both the daily and the testing, are being executed under the SA account.
** The maintenance plan is pointing to the correct directory of where the files are stored d:\backup\daily\
** I've turned on auditing of success/failures for all of the audit trials, and nothing critical/warning occur in the event logs for this transaction.
**The maintenance tasks execute successfully.
Finally, being completely irritated by the lack of deletion ability of SQL Server, I pointed the test maintenance task to a bunch of old SQL backup files and executed the script. SUCCESS! This to me implies that SQL can only delete SQL backups and it truly isn't file extension specific?
I could just being losing it, but I could have swore that the I got this working before.
Please verify my findings? (SQL only deletes SQL files)
-Brent
No comments:
Post a Comment