We help IT Professionals succeed at work.

Sharepoint 2007 to Sharepoint 2010 with separate MSSQL server

Apexadmin used Ask the Experts™
I currently have a 2003 server with Sharepoint 2007 on it. The databases are also on it, SQL 2008 express. I also have two virtual machines. One has SQL 2008 enterprise on it. The other is ready for Sharepoint 2010 to be installed.

I would like to migrate the databases to the dedicated SQL server and also upgrade to Sharepoint 2010. I am fairly new to this and have not performed this before. Can anyone provide me the best way to perform these actions and in what order as to not break the flow of production?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try http://technet.microsoft.com/en-us/library/cc263299.aspx

Install SharePoint 2010 on new server, pointing to new backend database too.

Add a temporary web application, so you can test things.

Set up the service applications you need.

Attach the database to your new SharePoint web farm.

Add a new web application to your new content database.

You will find list of errors and keep adding features / changing files to remove those errors.

Once the errors are removed then its time to move to production

Then make 2007 database read only.

Attach the database to your new SharePoint web farm.

Add a new web application to your new content database
Greg BurnsSQL / SharePoint Engineer
My $0.02:
On the old farm, you should run the STSADM -o PreUpgradeCheck.  This produces a nice HTML report of your farm configuration as well as any assemblies that are installed.  Keep this around because you'll need it for reference.

Then, on your new farm, you should use the heck out of the PowerShell command test-SPContentDatabase.  This will "pretend" to migrate your old content database into the farm, and will give you a report of any issues it finds.  It reports missing assemblies / solutions / features by GUID, which you can find with the PreUpgradeCheck report.  

You can also use some third party migration tools, which might pay for itself if you're doing a large migration:

Some other resources:

Buy this book: Professional SharePoint 2010 Administration

It's not focused on migration, but there is a chapter or two on the subject which is written in plain English, which I like.  I prefer to buy from Wrox because I can download the ebook immediately and get to work.  

The author of this book also wrote a nice white paper on migration, available free here:

Joel Oleson has also done a lot of work with migration:


First, thank you for the info. I am just getting my head through all this info. I am being very cautious about going through all this, we use sharepoint quite a bit and I don't want to break it. I haven't touched the old one yet. I installed SP 2010 on a new server and I am configuring it now.

I think my plan is to migrate the databases to the new SQL server first and see how that turns out before I attach the new SP to it and update it.

This article was in the article that was posted above http://technet.microsoft.com/en-us/library/ms345483(SQL.90).aspx

Is that the best way to do it or is that a bit dated?
Greg BurnsSQL / SharePoint Engineer

Your KB article is correct, and provides more detail for a detach/attach SQL migration than SharePointGirl's article.  The article is for SQL 2005 but it's essentially the same process in SQL 2008 and SQL 2008 R2.  

But I usually DON'T go for the detach/attach migration, because it's sort of a one-way trip.  You are actually moving and reusing the same database files, and as soon as you mount them in the new farm they undergo a schema upgrade, meaning you can't move them back if something fails.  This will of course impact your production farm  

You might want to use this method if you're planning to retire a physical machine and move all SQL components to a new server.  If this is your scenario, then I'd split it into two seperate tasks: moving the SQL instance; stabilizing; then doing the SharePoint migration.

But if you're JUST doing a SharePoint migration (and especially if you're just testing) I prefer to backup the content databases and restore them to another SQL instance (or to the same SQL instance using new names).

This way the old farm stays online while you play with the upgrade on the new farm.  Assuming, of course, that you have storage space for two copies of all your content databases.  


Thank you for all the help and info. I will plunge into this soon.