Media Family on Device X is Incorrectly Formed.

admin

The mystery of the INCOMPLETE backup set

It is the job of a good administrator to always be ready for a catastrophe. One of the biggest flaws of an administrator is to do backups and not test them. It seems simple, but you??ll be amaze at how many times I have ran into a site and not being able to restore a file from a backup set. While testing SQL backups for one of our clients, I ran into the following issue: A couple of the databases would not restore.

Here is more info about the problem. SQL 2000 on a Server 2003. Backups were done using SQL??s own backup system. All other backups would restore fine except for two. These were the largest backups of the whole set.

Here is the complete error message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
The media family on device ??backup.bak?? is incorrectly formed. SQL Server cannot process this media family. RESTORE DATABASE is terminated abnormally.

When viewing the backups?? content, it would show up a INCOMPLETE.

The way I test backups is by setting up a virtual machine with the applications needed and run the restore. I had set up a SQL 2000 with the latest service pack on top of server 2000.

With this information, you should be able to solve the problem. Read further if you want to know the solution.

I made one major mistake when testing these backups. My mistake was not using the exact conditions in which the backup were done.

I proceeded to set up another virtual machine with windows 2003 and SQL 2000. Then went back to the server and check its service pack level. It was service pack 3. This was the mistake. I was using the latest service pack which is service pack 4. Once I was on the right service back, the INCOMPLE message went away and I was able to restore the databases.

While researching the issue I ran into a lot of articles and posts without any resolutions. I ran into MS KB article 297104. This article explains that it is a known issue with SQL 2000, but their resolution did not work in this situation.

Lesson learned: Pay ATTENTION! I saw lots of post from people backing up a db and wiping out the server. Then not being able to restore the backup. Before working on a server, always document everything you can. Test the backups before moving forward. Always leave yourself a second and if possible a third way to get the data back.