January 4, 2012 at 4:41 am
Hi all,
I am hoping someone can help me with this, I have a SQL Server 2008 instance with build number 10.0.4000. It is due to be patched to SP3, though I see no evidence that SP3 contains a fix to my problem.
Basically, maintenance cleanup tasks are not working on my SQL backups. I have verified that the maintenance plans contain the correct paths, extensions, etc and that the folder and files have full permissions for the sqlservice account after finding a couple of previous threads dotted around these forums. I have also verified the backups using restore headeronly, however none of the suggestions I have found so far have worked.
I have got to a point where I am running tests using the xp_delete_file stored procedure both manually and within a plan against specific folders and individual files. When I run it against a folder it finishes with 'Completed successfully' even though the old files remain in the folder undeleted. If I run it against a specific backup file, I get the following error:
Msg 22049, Level 15, State 0, Line 0
Error executing xp_delete_file extended stored procedure: Specified file is not a SQL Server backup file.
This happens against backup files (.bak) I have created both manually and created using the maintenance plans. I have also tested this against compressed and uncompressed backup file.
Does anyone have any thoughts on how to resolve this or is it strongly believed SP3 addresses this issue?
Many thanks,
Stobe
January 4, 2012 at 8:47 am
what happens if you run
RESTORE HEADERONLY FROM DISK = N'm:\MSSQL\MYBAKFILE.BAK'
obviously substituting your drive path and filename details
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 4, 2012 at 8:50 am
Thanks for the reply Perry,
As I said up top, I have also verified the backups using restore headeronly. This returned the details as expected and didn't complain of any issues with the file.
Regards,
Stobe
January 4, 2012 at 9:57 am
sorry missed that bit, they're definitely native backups then?
How are you executing xp_delete_file?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 4, 2012 at 10:05 am
Yes, they are native/local backups. I am using the following syntax:
EXECUTE master.dbo.xp_delete_file 0,N'F:\<Path>',N'bak',N'2011-12-31T00:00:00',1
EXECUTE master.dbo.xp_delete_file 0,N'F:\<Path>\<Filename>.bak'
January 4, 2012 at 10:25 am
doesnt seem to much wrong with the syntax, remember that as an undocumented extended proc this is liable to change with any release.
I'm using SQL Server 2008 R2 SP1 and it behaves as expected.
was it working pre 10.0.4000, can you test this on an instance of 10.0.4000
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 4, 2012 at 10:29 am
It is strange, I have plenty of other 2005, 2008, 2008 R2 boxes where the cleanup tasks are working fine also.
Obviously I was only running the stored proc to try and get the output and see why the cleanup task was never deleting the files. Completely stumped what to do bar writing a custom script or proc now.
January 4, 2012 at 10:32 am
I use a simple VB script which works quite well at trimming backup files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2019 at 8:06 am
I know this is an old thread, but I came across a similar issue in SQL Express 2017
In my case xp_delete_file failed with same error. Installing Express as the default MSSQLSERVER instance rather than SQLEXPRESS resolved it for me.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply