February 27, 2006 at 8:58 am
This does not work. I have tried to run it from query analyzer as 'sa' and it still does not work. Does anyone know what I am doing wrong?
EXECUTE
master.dbo.xp_delete_file 0,N'D:\SQL2K5\BACKUP',N'.Bak',N'02/25/2006 09:45:07'
Thanks in advance
February 28, 2006 at 7:49 am
Does the login your Mssql server services run under have admin rights on the windows server?
February 28, 2006 at 8:15 am
Are there backup files from this server in there? From my testing it appears that this command will actually check to see if these are valid backups by running a restore headeronly on them.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 1, 2006 at 8:50 am
Sorry about the delay. I had the flu and was sleeping a lot. The server job is running as builtin/administrator.
The folder is shared just in case.
There are backup files from the SQL server in the specified location formatted this way
Lab_Testing_backup_200602260001.bak They are valid backups.
It allows me to delete them manually just not through that command. Is there another command I can use?
March 2, 2006 at 7:54 pm
I'm having the same issue. I have been told that SQL 2005 can't delete files in sub folders, but I've done a bunch of backups directly on the C drive and it still doesn't work. When I run it manually I get the following error:
Error executing extended stored procedure: Invalid Parameter
When I run it in a maintenance plan I get this error:
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'C:\\',N'bak',N'03/01/2006 11:10:07'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks Kris
Kris
March 2, 2006 at 10:18 pm
I have now got this working. It appears if you remove the time it will work. eg EXECUTE master.dbo.xp_delete_file 0,N'C:\aaa',N'bak',N'03/01/2006'.
However this does not appear to help when running an automatic maintenance plan.
Kris
April 26, 2006 at 2:21 am
Hi, I know its a late post to this but I had same issue and I think the above fix may be a bit misleading. I tested as above removing time and it still fails. I noted that the above solution has also modified the file extension 'bak' the maint plan generates code that is '.bak' if you remove the '.' as above and leave the time then it works. I think the solution is removing the'.' that the main plan generates (possibly in error) hence the maint plan failures.
Derek
April 26, 2006 at 5:26 am
I never did get that other solution to work. So you are saying that if I remove '.Bak' the delete will work? What would happen if I have anything else beside a .bak in that folder?
April 26, 2006 at 7:23 pm
I have left the time in and removed the '.' This 100% works for me. I have gone back to the maint plans and removed the '.' from these to, saved the plans and all is working as it should. Your last question is good as I never thought to check for this, not sure what it does. Not sure how it checks the filter, what if a file with the chars 'bak' exists in the directory, will it delete these also once the older than date is reached?? I will test but suggest you do also. Some doco on what the extended stored proc is doing internally would help here. Maybe the proc actually places a '.' and treats the 'bak' part as the extension, that would at least explain the removal of the extraneous'.' that meant no file matched previously.
April 27, 2006 at 7:51 pm
OK, I have tested and the following files in the same dir as backups :
testdocbakinfilename
testdocfilename.bak
testdocwithbakinfilenameand.txt
are not removed during cleanup. I have a feeling (maybe read somewhere) that the cleanup actually checks to verify that the file is an actual sql backup. So other files seem safe.
Derek
July 31, 2007 at 2:05 pm
Sorry for the double-post - but it is something I just seem to have figured out - I have been fighting with this for some time, but finally had some new ideas on an old topic...
(Originally posted in http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=379500&post=true) but this seems a more appropriate place to post this)
So rather than repeat myself - please go take a look (and at my update)
September 13, 2010 at 10:41 am
Thanks for the troubleshooting above. I think the root cause of this problem is when setting up the Maintenance Cleanup Task, you are asked for teh folder and "FILE EXTENSION" of teh backups you wish to delete. If you put a '.bak' in file extention it will not work. If you simply put 'bak' in the file extention it will.
Hope this helps.
September 17, 2010 at 2:23 pm
it should be bak not Bak
Regards
Sushant Kumar
MCTS,MCP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply