I need to start this post off by saying 2 little disclaimers:
A) don’t use stsadm in SharePoint 2010, I know it’s still supported but just don’t. It’s going away soon, has less functionality than PowerShell, and can cause some issues that may be unexpected. If you are having this issue as well, you are likely using it in a way that is unsupported anyway.
B) Microsoft does not support direct SQL edits on SharePoint farms. They never have and likely never will. This post has a fix to a specific issue, but I must stress that this fix is not supported by MS.
Now, on to our problem. SharePoint 2010 introduced a great new feature with its restore from unattached content database functionality. I use it all the time for restores of lists, documents, subsites, and collections. FYI, I have run into this issue several times when restoring lists, but have not even attempted the process since SP1 upgrade for sites, collections, etc. Now, if you notice, once you have browsed for an item to be pulled from the unattached content DB, SharePoint (and all the documentation) say to use Import-SPWeb PS cmdlet to import the list/site back into your environment. Before SP1, SharePoint would allow you to use stsadm -o import without complaining and it worked just fine (at least all the times I used it, remember it isn’t supported so I may have just been lucky). Now, after SP1, if you try to use stsadm to import a list, it will fail and on your site you will see some funky issues. You may experience the following:
Import fails saying List already exists with the same name (even if one doesn’t)
You cannot browse to the URL of the list in question to view content(don’t remember if it gives error of list has been deleted by another user or if it was a 404)
You will not be able to see the list at all in site content & structure admin page or in SharePoint Designer
You WILL be able to see the list in view all site content, but when you look at the URL, it does not take you to the URL with content for the list, but to another URL with the list GUID in it (again a lot of this is just from my notes/memory, sorry no screenshots or exact URL syntax). This page gives an error stating the list does not exist, may have been deleted by another user.
The content DB will not report the list as an orphan when you try to run a check.
You will NOT be able to delete the list normally. The list settings page will fail saying list does not exist, and PowerShell will fail with same error.
So, in essence you have a ghost list where you cannot see any of the content via designer or the browser and it looks like it shouldnt/doesn’t exist, but it is stuck in view all site content and prevents you from importing the list properly (as now, guaranteed you will have a list of same name, even though it doesn’t exist).
To remove this list, you will need to do some SQL queries (again, not supported by Microsoft). First, if you look at the URL from view all site content for the list, you can see the GUID. Copy the GUID down and find which content DB the site collection is in. Go to SSMS and run this query:
use <content_db>
select * from dbo.alllists
where tp_id = <list GUID>
You should see the info for your ghost list. Now, to get rid of it, just change “select” to “delete”:
use <content_db>
delete * from dbo.alllists
where tp_id = <list GUID>
Now, refresh your view all site content and the list should not show. OK, so the ghost list has been deleted, now let’s get that list imported in properly:
Import-SPWeb -Identity-Path
Simple enough, right. Oh, and just as a note, with Import-SPWeb you put just the site URL, not the URL to the list as you did with STSADM. We have started noticing this issue in 2010 SP1, even if it’s just an stsadm export, then stsadm import, so it may not be just that we were using it improperly but some changes in SP1 that may be forcing early depreciation of stsadm. I am not sure what the situation is for sure, but I have decided to just completely stop using stsadm altogether.