December 6, 2011 at 9:22 am
Has anyone experienced this:
I migrated a database from SQL 2005 SP3 to SQL 2008 R2 SP1. In 2005, the database has three logical files in three physical files. When I restored it to the new 2008 R2 server, I used the WITH MOVE option and specified new locations for the 3 files.
The next day, I took a full backup of the database on the 2008 R2 server. I tried restoring it to another 2008 R2 server, again using the WITH MOVE option and it failed. RESTORE with filelist only shows the database backup contains FOUR logical files! Select * from sys.database_files on the 2008 server shows only three logical files. If I go ahead and restore the backup to a new server, giving a file location for the fourth logical file, the restore completes and no physical fourth file is created. There is no entry for the fourth logical file in sys.database_files. DBCC CHECKDB returns no errors on the restored copy.
I believe the fourth logical file was added sometime in the past when we ran out of disk space (on the 2005 system), then deleted once the space issue was resolved. The strange thing is this database is used in log shipping and on 2005 and the restore portion of the log shipping routine works using only 3 logical file names. But on 2008 R2, that fourth one needs to be specified, even though it is never created and doesn’t show up in sys.database_files.
Anyone have any thoughts on this?
December 6, 2011 at 9:24 am
CSS or PSS.
Either there's something really wrong in the DB or you've hit a bug. Either way I'd like those guys on my side.
You can always wait 1-2 days before going there in case one of us has seen this before.
AFAIK, I've never seen that case here since 2005.
December 6, 2011 at 9:36 am
That's strange. If DBCC checks out, does the fourth file appear in sys.master_files?
I might call CSS as well.
December 6, 2011 at 9:42 am
It does not show up in sys.master_files.
December 6, 2011 at 9:52 am
I have seen this before, but it resolved for me by taking another full backup. It's a bug but not a harmful bug. it doesn't indicate corruption.
December 6, 2011 at 10:00 am
Robert - that's good to hear. But we've taken 4 full backups so far and the issue persists. Now these were all backups made with LiteSpeed. I'll try a native SQL backup after hours and see if that clears it. Thanks.
December 7, 2011 at 8:18 am
Here's a follow up. Last night, I made two native SQL full backups of the database on the 2008 R2 system back to back, followed by one LiteSpeed full backup. This morning, a RESTORE FILELISTONLY on both of the native SQL backup files did not show the extra logical file. However, it DID show up in the LiteSpeed backup.
I am currently running LiteSpeed 5.2, which I know is not the latest version. I'm also using compression, both in LiteSpeed and the native SQL Server backups.
So it looks like this might be a LiteSpeed issue. I certainly agree with Robert that it doesn't appear to be a corruption problem. Sounds like some old metadata is hanging around in the db somewhere and LiteSpeed is picking it up for some reason.
December 7, 2011 at 8:22 am
Thanks for the update.
Is there any way you can open a case with Quest and post back the results here? Might help some other people.
December 7, 2011 at 8:46 am
I did one more test. I took the native SQL backup I had made (that did NOT show the extra logical file), restored it, then took a LiteSpeed backup. The filelist from that LiteSpeed backup still shows the extra file.
December 7, 2011 at 8:52 am
Steve - Probably not anytime soon. Our support agreement with Quest is up and, although I'm in the process of renewing it, I don't know how long that will take. But I will add it to my to-do list.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply