I recently migrated a database from a SQL 2005 SP3 server to a 2008 R2 SP1 server. In the 2005 SQL Server, the database consisted of three logical and physical files. I moved it to the 2008 R2 server by taking a native SQL backup and restoring it on the new server using the WITH MOVE option to specify new locations for the files. No problems.

Later, I took a full backup using LiteSpeed on the 2008 R2 server and tried to restore it to another 2008 R2 server. It failed. After some troubleshooting, I discovered that when I did a RESTORE FILELISTONLY on the LiteSpeed backup file, it showed a fourth logical file! (I suspect this fourth file was added to address an out of disk space issue, then removed once the space issue was resolved. But this happened before I was here, so this is purely speculation based on the size of the database and the fact that the extra file was on a different drive than the others.)

On the live database, a SELECT * FROM sys.database_files only showed three files. Likewise, SELECT * FROM sys.master_files only showed three files for the database

I supplied a location for this fourth mystery file and restored the database. When the restore was finished, there was no physical fourth file and the two queries above showed only three files associated with the database. DBCC CHECKDB did not return any errors.

I posted this situation to the forums at SQL Server Central, and got a reply from Robert Davies (blog | Twitter) saying he had seen this situation before and that a subsequent backup cleared the problem. He felt this was a minor bug and not a database corruption issue.

I had taken a couple of backups since and the problem persisted, but they were all backups taken using LiteSpeed. So I proceeded to take back-to-back native SQL full backups (two for good measure), followed by a full LiteSpeed backup. A FILELISTONLY on both of the native SQL backups showed only three logical files. A FILELISTONLY on the LiteSpeed backup showed four files.

At this point, it would appear this is a LiteSpeed issue and that it is somehow picking up some old metadata. We are currently running LiteSpeed 5.2, which is not the latest version. Unfortunately, our support agreement is up and I cannot open a case with Quest. We are in the process of renewing the agreement and I’ll revisit this situation when that is complete and/or we move to the latest version of LiteSpeed.

One possible explanation: I don’t believe SQL 2008 R2 was out when LiteSpeed 5.2 was released. It’s possible that the database backup format has slightly changed and that is what is causing LiteSpeed to pick up this extra metadata. One way to test would be to move the database from the original 2005 server to a 2008 server and see if LiteSpeed behaves the same way. Unfortunately, I don’t have any SQL 2008 boxes to try this with. If this is the case, this represents one of the perils of using third party backup products.

UPDATE: Within 10 hours of this post appearing, I got an email from an engineer at Quest offering to help. It turns out that, yes, this behavior is in fact a bug and it was fixed in LiteSpeed 5.2 SP1. I hunted around my network and found I had that service pack tucked away somewhere. When I installed it, the problem disappeared. Big thanks to Quest for their unsolicited and speedy help on this!