Move-SPSite: Operation is not valid due to the current state of the object

Every day we face some strange issues in SharePoint, if it’s working before then why it suddenly stops working! Big question-hmm??

Today we are going to discuss one similar scenario and I am pretty much sure you will comes up with different resolution’s because this issue might vary from environment to environment so resolutions might be different.

Problem description:

One of our content database has already reached the 100GB mark so as per Microsoft recommendations, it’s better to move the site collections to new database as it might create some problems while doing backup and restore. 

So the plan to move some site collections from existing database to new database. For this purpose, we have created a new database by means of central administration.

One thing I wanted to clear that we are doing backup-restore activity, we are moving some heavy site collections between databases only.

In SharePoint 2010, we have an inbuilt command to move the site collections between databases, please refer the following:

Move-SPSite <http://ServerName/Sites/SiteName> -DestinationDatabase <DestinationContentDb>

So we make modifications as per our databases i.e. URL of the site collection and name of the destination database.

As soon as we hit enter, the PowerShell command throws an exception as mentioned below.

Error message:

Move-SPSite: Operation is not valid due to the current state of the object

Troubleshooting done:

-Checked the status of site collection-whether it’s set to lock/read only?

-Checked the status of the database-whether it’s online/offline?

-Tried detaching the source and destination databases and reattached again but still no results

-Tried moving site collections to existing databases- this one works!

-Tried creating a site collection in newly created database- works!

-The site that we have just moved to existing DB- tried moving to the destination database (where we are facing to problem)- Failed!

-Tried creating a new database for different web application and performed the same operation- Failed!

-checked the health analyser again to verify for any new errors

-Found this error which we have neglected-Databases running in compatibility range, upgrade recommended.

<This is the main culprit>

Resolution:

We ran this command on the source database from which we were trying to move the site collections to destination database.

upgrade-contentdatabase -Name <> -Webapplication <>

Operation completed successfully.

Tried running the move-spsite command and executed successfully without any issues…

By this way we have overcome this error message but as I mentioned in the beginning resolution might vary from environment to environment so request you to please share if you have different resolution part from the above mentioned so that it would be beneficial to others as well.

If you have any queries/questions regarding the above mentioned information then please let me know, I would be more than happy to help you as well resolves your issues, Thank you.

Product Applies To:

SharePoint Server 2010

SharePoint Foundation 2010

SharePoint Server 2013

How to add a database as a data source: SharePoint 2007

Before you begin to add a database as a data source, we will discuss about what is a data source.

Data Source:
A data store is a data repository of a set of integrated objects. These objects are modeled using classes defined in database schemas. Data store includes not only data repositories like databases, it is a more general concept that includes also flat files that can store data.

Connect to a database by saving the user name and password

When you use Connect to a database by saving the user name and password, Office SharePoint Designer 2007 generates a SQL-based connection string that stores the user name and password in the data source connection (a process known as SQL authentication). If your site is located on a server running Windows SharePoint Services 3.0, this is the preferred option for generating a connection string. You can also create a database connection by using a custom connection string. If the Data Source Library task pane is not visible, on the Task Panes menu, click Data Source Library.

1. In the Data Source Library task pane, under Database Connections, click Connect to a database.

If the Database Connections heading is collapsed, click the plus sign (+) to expand it.

3. In the Data Source Properties dialog box, on the Source tab, click Configure Database Connection.
4. In the Configure Database Connection dialog box, under Server Information, in the Server Name box, type the name of the server where your database resides.
5. In the Provider Name box, do one of the following:
• If you are connecting to an external Microsoft SQL Server 2000 database or an external Microsoft SQL Server 2005 database, click Microsoft .NET Framework Data Provider for SQL Server.
• If you are connecting to an external database that is OLE DB compatible, click Microsoft .NET Framework Data Provider for OLE DB.
6. Under Authentication, click Save this username and password in the data connection.
7. In the User name box, type your user name.
8. In the Password box, type your password.
9. Click Next.

Because SQL authentication saves the user name and password as text in the data connection, any user with permission to open the site in Office SharePoint Designer 2007 can view these credentials. A security warning informs you that the user name and password are saved as text in the data connection and that other site members can access this data source. If you want other site members to access the database by using the same user name and password, click OK. If you do not want other site members to access the database by using the same user name and password, click Cancel.

10. In the Database list, click the database that you want to use as a data source, and then do one of the following:
• Click Select a table or view, click the table or saved view that you want from the list, and then click Finish.
  By using this option, you create a default query that selects all records in the table or view. However, after you click Finish, you can modify the default query by clicking Fields, Filter, and Sort in the Data Source Properties dialog box. 
• Click Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures, and then click Finish.

This option is available only if your administrator has turned on the Enable Update Query Support option.

 

By using this option, you can create custom SQL commands. When you click Finish, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create commands and add parameters. After you create a custom SQL command, you can edit the query by clicking Edit Custom Query in the Data Source Properties dialog box. 

11. In the Data Source Properties dialog box, click the General tab, type a name for the data source, and then click OK.

The new database connection now appears in the Data Source Library task pane.

If you are trying to connect to an external server running SQL Server, make sure that SQL Server authentication is enabled on that server.

Reference: http://office.microsoft.com/en-ca/sharepoint-designer-help/add-a-database-as-a-data-source-HA010100908.aspx