Tuesday, May 3, 2011

SQL Server 2005 Maintenance Plan won't delete bakup 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:\GBDB\Log\log080411PM.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:\GBDB\Log\log040411PM.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 ?