How To Migrate SharePoint 2007 My Sites Databases to SharePoint 2010

By:   |   Comments (6)   |   Related: > SharePoint


Problem

I'm the DBA tasked with migrating the SharePoint 2007 content databases to our new SharePoint 2010 farm.  I'm having a real problem upgrading the My Sites content database; it takes 36 hours using the database attach method!  Our My Sites isn't mission critical but what I need is to be able to do this in a piecemeal fashion; e.g. I have a nightly maintenance window of a couple of hours and I can run a portion of the upgrade each night until it's done.  Do you have any ideas?

Solution

The SharePoint content databases hold all of your lists, documents, pictures, etc.  In many cases they can grow very quickly as users add content. In order to proactively manage the growth of your content databases, you may want to create additional content databases and move selected site collections from one content database to another.  This allows you to keep the size of the content database somewhat in check.  The larger the content database the longer it takes to backup, restore, run DBCC, etc.

In the case of migration from SharePoint 2007 (SP2007) to SharePoint 2010 (SP2010), you can backup your SP2007 content databases and "attach" them to your SP2010 farm.  This is called the database attach method and it also performs whatever upgrades are required; e.g. schema changes.  If you are new to SharePoint, you may want to take a look at my earlier tip Introduction to SharePoint for SQL Server DBAs where I quickly summarize what you was an DBA need to know about SharePoint.

I have experienced the exact situation that you cite; my SP2007 My Sites content database took 39+ hours to attach to SP2010.  I have no idea why it takes so long but since a migration is a one-time thing I don't want to spend any time worrying about it; I just needed to get it done.

The following are the steps that I used to perform a piecemeal upgrade of my SP2007 My Sites content database to SP2010:

  1. I decided to split the My Sites content database into 5 separate databases so I needed to create additional content databases.  This allowed me to upgrade a group of My Sites each night.
  2. Get the list of My Sites (each is a site collection) then decide on which site collections to move to each content database.
  3. Move the site collections to their new content database.
  4. Backup the newly created and populated content databases in the SP2007 farm.
  5. Restore the content databases to the SP2010 database server.
  6. Attach each content database to the SP2010 web application for My Sites.

The above steps make the following assumptions:

  • You have a database server for your SP2007 and a different database server for you SP2010 farm
  • You are using Windows authentication for SQL Server (if you use SQL Server authentication you'll need to supply additional parameters to the commands that we will go though below)

I'll use the STSADM command-line administration tool for the steps that are performed in the SP2007 farm; by default you will find STSADM.EXE in the folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN. You will probably want to add the folder to your path. You run STSADM from a command prompt. I'll use PowerShell cmdlets for the steps that are performed in the SP2010 farm; to run the cmdlets you can launch the SharePoint 2010 Management Shell via the Microsoft SharePoint 2010 Products program group in the Start menu.

Create Content Databases

Use the following command to create a new content database in your SP2007 My Sites web application:

STSADM -o addcontentdb -url http://mysites.yourdomain.com -databasename MySites_Content_01 -databaseserver yourdbserver

The following are the main points about the parameters:

  • The o parameter is the operation name
  • The url parameter is your My Sites web application url
  • The databasename parameter is the name of the content database to be created
  • The databaseserver parameter is the server name of your SP2007 database server

You can find the details on all of the command line parameters for adding a content database here.  Make sure to check the sizes of the database file and log file and grow them as necessary; the default is 20MB for data and 5MB for the log.

Get the List of My Sites

Use the following command to get the list of my sites (each My Site is a site collection) in your SP2007 My Sites web application:

STSADM -o enumsites -url http://mysites.yourdomain.com > sitelist.xml

The following are the main points about the parameters:

  • The o parameter is the operation name
  • The url parameter is your My Sites web application url
  • You need to redirect the standard output from the command to a file; e.g. sitelist.xml

Here is an example of the sitelist.xml file:

Enumerate My Sites XML file

Note that the file has the size of each My Site (StoreageUsedMB); you can use this to properly size the content databases where you will move the sites.

Move a Site Collection to Another Content Database

To move site collections to another content database, you have to create one XML file for each target content database.  The format of the XML file must be just like the sites.xml file created in the previous step and it must include the just list of sites to be moved.  So if you wanted to split your My Sites into 5 content databases, you would need 5 files.  After creating the XML files with the list of sites for each content database, run the following command (all on one line for each content database move):

STSADM -o mergecontentdbs -url http://mysites.yourdomain.com -sourcedatabasename Content_MySite -destinationdatabasename dbname -operation 3 -filename C:\temp\mysites-01.xml

You can find the complete details on the mergecontentdbs operation here.  Note that when the above command completes you will see a message that an IISRESET is required; run the following command from the command prompt (you will likely have to launch the command prompt with the Run As Administrator):

IISRESET /noforce

I find conflicting information concerning whether or not you need to run the STSADM command preparetomove before moving sites to another content database.  You can find the details for the preparetomove command here; I've never been able to specify the command and have it run successfully; I always get some error message that doesn't help me figure out what I'm doing wrong.  In the case of migrating SP2007 My Sites to SP2010, I have not run the command and the migration was successful.

Backup and Restore the New Content Database(s)

Assuming you have separate database servers for your SP2007 and SP2010 farms, you will need to backup the new content databases on the SP2007 database server, copy the backups to the SP2010 database server, and restore them to the SP2010 database server.  You can use the SQL Server Management Studio gui (right click on the database, select Tasks, Backup) or simply enter the T-SQL command in a query window:

BACKUP DATABASE contentdbname TO DISK = 'fullpathofbackupfile'

To perform the restore you can use the SQL Server Management Studio gui (right click on the database, select Tasks, Restore) or simply enter the T-SQL command in a query window:

RESTORE DATABASE contentdbname FROM DISK = 'fullpathofbackupfile'

Prior to taking the backup, you may want to set the content database to read only so users cannot make any changes once the migration is in process. 

Attach the Content Databases to the SP2010 Farm

After you restore the new content databases to your SP2010 database server, you are ready for the final step - attach the databases to your SP2010 My Sites web application.  STSADM is still available with SP2010 but the preferred approach is to use PowerShell cmdlets for administration tasks.  Launch the SharePoint 2010 Management Shell from the Microsoft SharePoint 2010 Products program group in the Start menu; you will see what looks just like a command prompt but it is in fact a PowerShell command prompt.

Run the following command to attach and upgrade your SP2007 content database for each content database to be attached:

Mount-SPContentDatabase dbname -DatabaseServer dbservername -WebApplication http://mysites.yourdomain.com -UpdateUserExperience

Specify the UpdateUserExperience parameter to have the My Sites use the SharePoint 2010 user interface.  You can omit the parameter and have the My Sites retain the SharePoint 2007 look and feel.  I tried this and I didn't like the results so I would suggest that you try the SharePoint 2010 user interface; it just works better.

You can find the full details for the Mount-SPContentDatabase cmdlet here. 

Next Steps
  • Although the focus of this tip was migrating My Sites, the steps apply to migrating any SP2007 content database to SP2010 and also moving site collections between content databases.
  • Take a look at the tips on MSSQLTips.com in the SharePoint category; most of these are written specifically for DBAs.
  • You can find the details on all of the STSADM database operations here.
  • You can find the details on all of the PowerShell cmdlets for database operations here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, June 20, 2013 - 11:40:43 AM - Kris Back To Top (25505)

Hello,

I get several errors when migating a content db:

Database contains a site (Id = ., Url = .) that is not found in the site map. Consider detach and reattach the database.
The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.
Database contains a site (Id = ., Url = .) whose url is already used by a different site, in database (Id = [.-.-.-.-.], name = [.]), in the same web application. Consider deleting one of the sites which have conflicting urls.
The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.
One or more setup files are referenced in the database [.], but are not installed on the current farm. Please install any feature or solution which contains these files.
Template MPS#0: SPSite Id=bcb4ff41-ca72-4738-84d1-a7d83767ca35 could not be accessed due to exception. Skipping SPWeb Id=. for template upgrade. Exception: System.IO.FileNotFoundException: The site with the id . could not be found.

Any help would be great!

Monday, November 12, 2012 - 2:15:34 PM - AMOL GHUGE Back To Top (20296)

Thank you very much for publishing this vital information !!

Monday, October 1, 2012 - 12:24:53 AM - YetanotherSP User Back To Top (19749)

Thanks Ray! I made quite a few mistakes along the way... which led to the troubles I reported. First of, there was a typo with the database name (duh!) when adding the content database to the web application. No wonder it showed 0 sites... so that got resolved when I caught that oversight. As far as the URL of the site collections are concerned, that seemed to have got resolved in the process of adding the content database to the web application.

All this is part of a migration project, where I am trying to migrate a significantly sized content database (1.25 TB) from SPS 2003 to MOSS 2007. Thanks, I appreciate your quick response!

Sunday, September 30, 2012 - 7:07:48 PM - Ray Barley Back To Top (19748)

A content database is added to a web application.  In your case you need a web application with the URL http://portalnew.  You specify

-WebApplication http://portalnew in the Mount-SPContentDatabase command.  Then the URL of your site collection(s) in the content database begin with http://portalnew.

You can create a web application using Central Administration or New-SPWebApplication PowerShell command (http://technet.microsoft.com/en-us/library/ff607931.aspx)  You may need to specify http://portalnew as a "host header"

 

Sunday, September 30, 2012 - 8:46:38 AM - YetanotherSP User Back To Top (19747)

If I take the database migration route to migrate a content database from 2007 to 2010, and the URLs of the two portals are different (e.g. http://portalOld and http://portalNew), how do I fix this. I attached the database on the new server and added the content database in the new portal's Central Administration. But it shows up as 0 sites.

Thursday, July 5, 2012 - 7:57:14 AM - Saif Khan Back To Top (18354)

You can easily restructure your websites into different content databases from 2007 to 2010 with migration tools. A good tool that I found is dSHIFT Migrator for SharePoint and dSHIFT Migrator for SharePoint Online. These tools help you migrate, restructure ands reorganize your content the way you want. 














get free sql tips
agree to terms