content missing after restore in sharepoint 2013

Question

I have a site collection in a content database on my prod sp2013 server, as reported by get-spsite http://siteurl | get-spcontentdatabase. It is the only site collection in that content database, although I have about 30 content databases total.

if I take a SQL backup of that database and restore it to a dev SQL server, then mount it to my dev sharepoint environment, that restored site is missing lots of list items (about 900) and it shows last modified 4 years ago, where the site is being modified daily in production.
If I take a backup of the site collection by backup-spsite http://siteurl -path c:\backuppath.bak and then restore that site to my dev environment with restore-spsite http://siteurl -path c:\backuppath.bak that restored site is also missing that same large quantity of list items.
I've done this repeatedly, with multiple SQL backups, and site collection backups.
I am at a loss as to what could be going on here. All I can figure is that in prod environment, the site must not actually be in the database that is being reported (or is somehow in two content databases).

Any ideas what is going on, or how to resolve this? I'm about to apply a Cumulative Update and want to ensure I have good backup of this site, or can recover it to a temporary location if necessary.

thanks

Answer 1

looking at this(https://www.mssqltips.com/sqlservertip/5989/sharepoint-backup-and-restore/) backup/restore article you sent me to, I tried the 'recover content from unattached database' and saw that it also showed two instances of the same site collection in that database.
A google search lead me to this page https://social.technet.microsoft.com/wiki/contents/articles/3837.how-to-identify-and-clean-up-orphan-sites-and-orphan-contentdatabases-in-sharepoint.aspx
which included a command to delete a site by site ID from a particular database - I executed this command:
stsadm -o deletesite -databaseserver spsqldev -databasename contentDBName -siteid "4f8d9832-196c-4ecc-ae4d-56e05ff25c44" -force

Then dismounted the content database and re-mounted it. After that, the content database showed the current content!
I tested this twice in two separate dev environments. I'll now do the same procedure in prod.

Thanks for your response, which pointed me in the right direction.

Answer 2

Hi John,

Ensure you have the last successful backup and you restore from there.

You can try to sort by "date" to know the latest backup (example :C:\Backup\site_name.bak or c:\backuppath.bak (as in your_case) )

Example : Restore-SPSite https://server_name/sites/site_name -Path C:\Backup\site_name.bak

There are some steps here which may help.
https://www.mssqltips.com/sqlservertip/5989/sharepoint-backup-and-restore/

Answer 3

I have verified that I have the right SQL backup file repeatedly.
I just looked at the database I restored from backup, and if I query the [allDocs] table in the restored database , I can see entries with "TimeLastModified" and "TimeCreated" field values in the last couple of days - and I can see item IDs for the list which I don't see in the actual site.

select top (1000) * FROM [ContentDBName].[dbo].[AllDocs]
where dirname like '%/lists/task%' order by TimeLastModified desc

Answer 4

more info: when I query the content database directly
SELECT [Id] ,[TimeCreated] ,[Deleted] ,[LastContentChange]
FROM [contentDB].[dbo].[AllSites]
it returns TWO entries with different ID values - the older one matches the 'last change 4 years ago' value

Id TimeCreated Deleted LastContentChange
4F8D9832-196C-4ECC-AE4D-56E05FF25C44 2012-06-28 17:57:47.000 0 2017-06-18 02:38:38.000
4815A126-C7DA-4291-B78E-C07A0E947448 2012-06-28 17:57:47.000 0 2020-12-10 16:31:33.903

in PROD, when I do get-spsite http://sitename | select ID
it returns ID: 4815a126-c7da-4291-b78e-c07a0e947448
in DEV it returns ID: 4f8d9832-196c-4ecc-ae4d-56e05ff25c44

So, it appears somehow there are two instances of that same site collection in the content database, PROD is using one, and when I restore to dev, it attaches the other.

how can I get rid of the old(?)/obsolete copy from the database?