THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Uri Dimant

SQL Server 2005 Maintenance Plan won't delete backup files created in SQL Server 2008

I have been asked recently to help to one of my friends with "strange behaviour" as he described...He has SQL Server 2008 where sql job copies .BAK files (database backup) into his local disk drive with requirement to keep those file only for one month. So as he has SQL Server 2005 (SP3) installed on his machine the first idea was to create Maintenance Clean Up task to delete the files. But as you imagine that did not work. Surely, when I came to help, I did not know that those files are created in SQL Server 2008 and has been copied to his local machine and after cheching all possibilities I launch up SQL Server Profiler to see what is going on... I saw the below command to be executed for each file to be deleted .

exec sp_executesql N'RESTORE LABELONLY FROM DISK=@P1',N'@P1 nchar(27)',N'X:\DB\Log\log010411PM.bak'

Well, I put that statement in Query window and obviously got the error which leads me to the root of  the problem.

Msg 3241, Level 16, State 7, Line 1

The media family on device 'X:\DB\Log\log010411PM.bak' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE LABELONLY is terminating abnormally.

 What do you think guys? Does SQL Server need to check out the version of the files need to be deleted or just delete files with .BAK extension regardless on version they were created ?


Published Tuesday, May 3, 2011 5:24 AM by Uri Dimant



Michael K. Campbell said:

I'm sure there's someone, somewhere, that would have very good arguments for WHY it's doing what it's doing. But to me, I'd prefer that it just deleted whatever file extension you punched into the Maintenance Plan task - since it does, indeed, make you define which extension you're targeting. So, seems to me that once that ship sails, it should just delete anything with that extension > x.

May 3, 2011 11:01 AM

Andy Kelly said:

xp_filedelete is what the MP use to delete backup files and it has always checked to see if the file is indeed a valid SQL Server backup file in a format it recognizes. It does not go by the extension it actually reads the header of the file and if it is not a backup file it won't delete it. I wish this wasnt the case but that is the way it is and I don't see it changing. So it is not suprising that 2005 cannot recognize a 2008 backup file, especially if it was compressed.

May 3, 2011 2:37 PM
New Comments to this post are disabled

About Uri Dimant

Uri Dimant
Privacy Statement