Database-Attach Migration Approach for SharePoint 2010 Upgrade

Advantage:

Almost no downtime! While upgrading, the database would be read only.

Easy to perform the upgrade.

Upgrade the databases in any order.

 

Disadvantage:

No settings, services, or customizations are upgraded or moved.

A database attach upgrade takes less time to complete but requires more time to be spent on manually moving and adding settings, permissions, and customizations.

 

High Level Procedure:

1.      First set up new SharePoint Foundation server or farm, and then set the old farm databases to read-only.

2.      Back up content databases, and restore them to the new SharePoint Foundation server.

3.      Switch users to the new farm.

 

Requisite:

The server and farm settings are not upgraded. You must manually transfer settings that you want to preserve from the old farm to the new farm. Any customizations must also be transferred to the new farm manually. Any missing customizations may cause unintended losses of functionality or user experience issues.

Direct access to the database servers is required.

 

Detailed Procedure:

1.      Set old databases to read-only.

a)        Access to old farm database server, open SQL Server Management Studio.

b)        Right click each database, select Properties -> Options, turn Database Readonly to True.

c)        Repeat above process to set every databases to be migrated to read-only.

2.      Backup content databases.

a)        Right click each database, choose Task -> Backup, and Set backup Type to Full.

b)        Click OK to start the backup.

c)        Repeat above process to backup all content databases.

3.      Restore the database to the new SharePoint SQL Server.

a)        Open SQL Server Management Studio in the new farm.

b)        Right-click the Databases folder and select Restore Database.

c)        Specify the Restore To database name, the source is from Device, specify the proper source file, then click OK to start restore.

d)       Make sure all databases restored are NOT read only. See step 1 to configure.

4.      Attach the databases to SharePoint (with PowerShell).

a)        Open PowerShell by going to Start -> All Programs -> Microsoft SharePoint 2010 Products folder -> SharePoint 2010 Management Shell.

b)        Use stsadm command to attach the content databases:
Stsadm.exe -o addcontentdb -url http://YOURROOTSITE -databasename <YOUR_DB_NAME>

c)        Or use cmdlet:
Mount-SPContentDatabase -Name YOUR_DATABASE_NAME -WebApplication http://YOUR_ROOT_SITE

d)       There is another cmdlet, Upgrade -SPContentDatabase, that is often confused with Mount -SPContentDatabase. The Upgrade cmdlet is used only if your database upgrade fails and you need to retry.

5.      Switch user to the new farm by change the FQDN ip.

 

 

6.      For step 1 – 3, there’s another way takes less time but required stopping the SQL service.

a)        We could take the SQL service/(or content databases) offline

b)        Copy and paste the content databases to the new farm

c)        Simply Attach them to the SQL server.

d)       And then follow the Step 4 and 5 to finish.