Error: Violation of UNIQUE KEY constraint 'Docs_IdLevelUnique'

When trying to restore a site collection to a content database through PowerShell command "Restore-SPSite", I got the error message below.


System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'Docs_IdLevelUnique'. Cannot insert duplicate key in object 'dbo.AllDocs'. The duplicate key value is (bd416c52-fd3f-477e-b067-5a20ff243896, 1).  The statement has been terminated.    
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    
 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    
 at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()    
 at System.Data.SqlClient.SqlDataReader.get_MetaData()    
 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    
 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    
 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    
 at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    
 at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)    
 at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)

at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)    
 at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock)    
 at Microsoft.SharePoint.Library.SPRequestInternalClass.RestoreSite(String bstrUrl, String bstrFileName, Boolean bCurrentServerADMode, Int32 major, Int32 minor, Int32 build, Int32 revision)    
 at Microsoft.SharePoint.Library.SPRequest.RestoreSite(String bstrUrl, String bstrFileName, Boolean bCurrentServerADMode, Int32 major, Int32 minor, Int32 build, Int32 revision)    
 at Microsoft.SharePoint.SPSite.Restore(String filename, Boolean isADMode)    
 at Microsoft.SharePoint.Administration.SPSiteCollection.Restore(String strSiteUrl, String strFilename, Boolean bOverwrite, Boolean bGradualDelete, Boolean hostHeaderAsSiteName)    
 at Microsoft.SharePoint.PowerShell.SPCmdletRestoreSite.InternalProcessRecord()    
 at Microsoft.SharePoint.PowerShell.SPCmdlet.ProcessRecord()    
 at System.Management.Automation.CommandProcessor.ProcessRecord()    
 at System.Management.Automation.CommandProcessorBase.DoExecute()    
 at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)    
 at System.Management.Automation.PipelineNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)    
 at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)    
 at System.Management.Automation.StatementListNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)    
 at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)    
 at System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(ParseTreeNode ptn, Array inputToProcess)    
 at System.Management.Automation.ScriptCommandProcessor.RunClause(ParseTreeNode clause, Object dollarUnderbar, Object inputToProcess)    
 at System.Management.Automation.CommandProcessorBase.DoComplete()    
 at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)    
 at System.Management.Automation.Runspaces.LocalPipeline.InvokeHelper()    
 at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()    
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    
 at System.Threading.ThreadHelper.ThreadStart()

SqlError: 'Violation of UNIQUE KEY constraint 'Docs_IdLevelUnique'. Cannot insert duplicate key in object 'dbo.AllDocs'. The duplicate key value is (bd416c52-fd3f-477e-b067-5a20ff243896, 1).'    Source: '.Net SqlClient Data Provider' Number: 2627 State: 1 Class: 14 Procedure: '' LineNumber: 1 Server: 'DB_SharepointQA\SHAREPOINT'

SqlError: 'The statement has been terminated.'    Source: '.Net SqlClient Data Provider' Number: 3621 State: 0 Class: 0 Procedure: '' LineNumber: 1 Server: 'DB_SharepointQA\SHAREPOINT'

I know this is because I deleted the same site collection before, and some data are actually still there in the content database. However, it seems there is no easy way to fix it! Thanks for the post here, I realized that the only solution might be moving site collections to another content database.

Below is the script. I recreated the content database because want to keep the same database name.


New-SPContentDatabase SP_Content_team80_new  -WebApplication http://sitename

Get-SPSite -ContentDatabase SP_Content_team80 | Move-SPSite -DestinationDatabase SP_Content_team80_new

Get-SPContentDatabase -Identity "SP_Content_team80" | Remove-SPContentDatabase

New-SPContentDatabase SP_Content_team80  -WebApplication http://sitename

Get-SPSite -ContentDatabase SP_Content_team80_new | Move-SPSite -DestinationDatabase SP_Content_team80

Get-SPContentDatabase -Identity "SP_Content_team80_new" | Remove-SPContentDatabase

Can we use "Gradual Site Delete timer job" to do the job?  Not sure. I didn't specify "GradualDelete" parameter when deleting the site collection, so it should not work in theory. Any way, I forgot to try that before moving all those site collections.