How To Migrate SharePoint 2007 My Sites Databases to SharePoint 2010

By: Ray Barley   |   Updated: 2012-05-03   |   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:

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:

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:

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):

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):

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:

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:

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:

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.

About the author

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