Attach databases and upgrade to SharePoint Foundation 2010

Applies to: SharePoint Foundation 2010

When you upgrade from Windows SharePoint Services 3.0 to Microsoft SharePoint Foundation 2010 by using the database attach upgrade approach, you upgrade only the content for your environment and not the configuration settings. Using a database attach upgrade approach is useful when you are changing hardware or want to reconfigure your server farm topology as part of the upgrade process. For more information about how to choose an upgrade approach, see Determine upgrade approach (SharePoint Foundation 2010).

The first step in the process is to set up a new environment to host the upgraded content. If you have not yet set up and configured the new environment, follow the steps in Prepare the new SharePoint Foundation 2010 environment for a database attach upgrade to do so. You must create a Web application in the new environment for every Web application in the old environment, copy any server-side customizations, configure services, and apply any farm settings to the new environment.

After you set up the new environment, you can use the procedures in this article to detach and then reconnect the databases to perform the actual upgrade. This article contains the steps required to perform a standard database attach upgrade and a database attach upgrade with read-only databases.

In this article:

• Process overview

• Before you begin

• Set the previous version databases to be read-only (database attach with read-only databases)

• Back up the previous version databases by using SQL Server tools

• Detach the previous version databases (standard database attach)

• Restore a backup copy of the database (database attach with read-only databases)

• Set the databases to read-write (database attach with read-only databases)

• Verify custom components

• Attach a content database to the Web application

• Verification: Verify upgrade for the first database

• Attach remaining databases

• Verification: Verify upgrade for additional databases

For more information about the general process of upgrading by using the database attach upgrade approach, see Upgrade process overview (SharePoint Foundation 2010).

Process overview

When you upgrade by using database attach upgrade, you detach the databases in the old farm and then attach them to the new farm. When you attach a database to the new farm, the upgrade process runs and upgrades the whole database. The database attach upgrade process is similar to the in-place upgrade process. The difference is that the database attach upgrade process is performed manually, and is performed in a separate environment.

If you want to preserve your original farm and allow users to continue to access their data, you must set the databases to read-only and then attach a backup copy of the databases.

For a general overview of the upgrade process, see Upgrade process overview (SharePoint Foundation 2010).

Before you begin

Before you begin the database attach upgrade, review the following information about permissions, hardware requirements, and software requirements. Follow the specified steps to install or configure prerequisite software or to modify settings.

• Ensure that you have met all hardware and software requirements. You must have a 64-bit version of Windows Server 2008 or Windows Server 2008 R2. For server farms, you must also have a 64-bit version of SQL Server 2005 or SQL Server 2008. For more information about these requirements (such as specific updates that you must install), see Determine hardware and software requirements (SharePoint Foundation 2010).

• Ensure that you are prepared to set up the required accounts by using appropriate permissions. For detailed information, see Administrative and service accounts required for initial deployment (SharePoint Foundation 2010).

• Ensure that the account you use to attach the databases is a member of the db_owner fixed database role for the content databases that you want to upgrade.

• Run the pre-upgrade checker tool on the sites that are stored in the databases. The pre-upgrade checker identifies potential upgrade issues in your environment so that you can address them before you upgrade. For more information, see Run the pre-upgrade checker (SharePoint Foundation 2010).

• Create a new server farm environment. For information about how to create the new environment, see Prepare the new SharePoint Foundation 2010 environment for a database attach upgrade.

• Check for and repair any database consistency errors. For more information, see Database maintenance for Windows SharePoint Services 3.0 (white paper).

Set the previous version databases to be read-only (database attach with read-only databases)

If you are using the read-only databases hybrid approach to upgrade, set the previous version databases to read-only before you back up the databases. In any type of database attach upgrade, you can also set the databases to read-only temporarily to ensure that you capture all the data in the backup so that you are restoring and upgrading the current state of the environment. If the databases are set to read-only, users can continue to view content, but they will be unable to add or change content.

To set a database to read-only in SQL Server 2000

1. In SQL Server Enterprise Manager, right-click the name of the database that you want to set to read-only, and then click Properties.

2. In the Properties dialog box, click the Options tab.

3. Under Access, select the Read-only check box, and then click OK.

To set a database to read-only in SQL Server 2005

1. In SQL Server Management Studio, right-click the name of the database that you want to set to read-only, and then click Properties.

2. In the Select a page section, click Options.

3. In the right pane, under Other options, in the State section, next to Database Read-Only, click the arrow, and then select True.

To set a database to read-only in SQL Server 2008

1. In SQL Server Management Studio, in Object Explorer, connect to an instance of the Database Engine, expand the server, and then expand Databases.

2. Select the database that you want to configure to be read-only, right-click the database, and then click Properties.

3. In the Database Properties dialog box, in the Select a page section, click Options.

4. In the right pane, under Other options, in the State section, next to Database Read-Only, click the arrow, and then select True.

You can configure the READ_ONLY database availability option by using Transact-SQL. For more information about how to use the SET clause of the ALTER DATABASE statement, see Setting Database Options (https://go.microsoft.com/fwlink/p/?LinkId=148362).

Back up the previous version databases by using SQL Server tools

Follow the appropriate procedure to back up databases in SQL Server 2000, SQL Server 2005, or SQL Server 2008. Repeat these steps for each content database in your server farm.

You do not have to back up the configuration or admin content databases, because you will re-create these databases in the new server farm. For more information about the kinds of databases that you might have in a Windows SharePoint Services 3.0 server farm, see Database types and descriptions (Windows SharePoint Services 3.0).

At the end of this procedure, you will have created duplicates of the read-only content databases.

To back up a database in SQL Server 2000

1. On the database server, click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2. In SQL Server Enterprise Manager, expand Microsoft SQL Servers.

3. Expand SQL Server Group.

4. Expand (local) (Windows NT).

5. Expand Databases.

6. Right-click the database that you want to back up, point to All Tasks, and then click Backup Database.

7. In the SQL Server Backup dialog box, in the Name box, specify a name for the backup, and then in the Backup area, select Database - complete.

8. In the Destination area, either select an existing destination or do the following:

   1. Click Add.

   2. In the Select Backup Destination box, select File Name, and then next to the File Name box, click Browse.

   3. In the Backup Device Location - (local) dialog box, in the File name box, type a file name, and then click OK.

   4. Click OK again to close the Select Backup Destination dialog box.

9. Click OK to start the backup process.

10. Click OK to acknowledge that the backup process is complete.

Repeat the previous procedure to back up all the other content databases that are used by Windows SharePoint Services 3.0 in your environment.

To back up a database in SQL Server 2005

1. On the database server, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. In the Connect to Server box, fill in the connection information, and then click Connect.

3. After you connect to the appropriate instance of the SQL Server 2005 Database Engine, in Object Explorer, expand the server tree by expanding the server name.

4. Expand Databases, right-click the database that you want to back up, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

5. In the Source area, in the Database box, verify the database name.

6. In the Backup type box, select Full.

7. Under Backup component, select Database.

8. In the Backup set area, in the Name text box, either accept the default backup set name that is suggested or type a different name for the backup set.

9. In the Destination area, specify the type of backup destination by selecting Disk or Tape, and then specify a destination. To create a different destination, click Add.

10. Click OK to start the backup process.

Repeat the previous procedure to back up all the other content databases that are used by Windows SharePoint Services 3.0 in your environment.

To back up a database in SQL Server 2008

1. On the database server, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server box, fill in the connection information, and then click Connect.

3. After you connect to the appropriate instance of the SQL Server 2008 Database Engine, in Object Explorer, expand the server name.

4. Expand Databases, right-click the database that you want to back up, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

5. In the Source area, in the Database box, verify the database name.

6. In the Backup type box, select Full.

7. Under Backup component, select Database.

8. In the Backup set area, in the Name text box, either accept the default backup set name or type a new name.

9. In the Destination area, specify the type of backup destination by selecting Disk or Tape, and then specify a destination. To create a different destination, click Add.

10. Click OK to start the backup process.

Repeat the previous procedure to back up all the other content databases that are used by Windows SharePoint Services 3.0 in your environment.

Detach the previous version databases (standard database attach)

Before you can attach your databases to the new environment and upgrade the data, you need to detach them from the current environment. After you have detached the databases, you can move them to a new database server or leave them on the existing database server and attach them to the Web applications.

To detach a content database from a Web application

1. In Central Administration, on the Application Management page, in the SharePoint Web Application Management section, click Content databases.

2. On the Manage Content Databases page, click the content database you want to detach.

   Note

   If the content database does not appear, it might be associated with another Web application. To select another Web application, on the Web Application menu, click Change Web Application.

3. On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check box, and then click OK.

   Note

   Removing the content database does not delete the database; it only removes the association of the database with the Web application.

4. Repeat steps 2 and 3 for each content database that you want to detach.

You can also use the deletecontentdb Stsadm operation to detach a content database from a Web application. For more information, see Deletecontentdb: Stsadm operation (Windows SharePoint Services).

If you are moving the databases to a different database server, you must also detach the databases from the instance of SQL Server before you move them and attach them to the new instance of SQL Server after you move them.

To detach a database from an instance of SQL Server and move it to another instance of SQL Server

1. In SQL Server 2005 Management Studio, open the source instance of SQL Server, and then expand the Databases node.

2. Right-click the content database, point to Tasks, and then click Detach. Repeat this step for each content database that you want to detach and move.

   Note

   Use this procedure to move only content databases. Do not detach any other databases.

3. In Windows Explorer, browse to the location of the .mdf and .ldf files for the content databases.

4. Select the .mdf and .ldf files for the database you want to move and either copy or move them to the destination directory.

5. In SQL Server 2005 Management Studio, open the source instance of SQL Server.

6. Right-click the Databases node, point to Tasks, and then click Attach.

7. In the Attach Database dialog box, browse to the location to which you transferred the .mdf and .ldf files, select the .mdf file for the database you want to attach, and then click OK.

8. Repeat steps 6 and 7 for each content database that you are moving.

Restore a backup copy of the database (database attach with read-only databases)

After you configure the new server farm, you can restore the backup copies of the databases on one of the following: Microsoft SQL Server 2008 R2, SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2, and SQL Server 2005 with SP3 and Cumulative Update 3. Note that you must restore to a 64-bit version of SQL Server 2008 R2, SQL Server 2008 with SP1 and Cumulative Update 2, and SQL Server 2005 with SP3 and Cumulative Update 3. Start with one database, and then verify that the restoration has worked before you restore the other databases.

The following section provides procedures for restoring the backups.

To restore a backup copy of a database in SQL Server 2005 Enterprise Edition

1. In SQL Server Management Studio, right-click Databases, and then click Restore Database. The Restore Database dialog box appears.

2. In the Restore Database dialog box, on the General page, in the To database box, type the name of the database you are restoring.

3. In the To a point in time text box, keep the default (Most recent possible).

4. To specify the source and location of the backup sets to restore, click From device, and then click Browse to select the backup file.

5. In the Specify Backup dialog box, in the Backup media box, make sure that File is selected.

6. In the Backup location area, click Add.

7. In the Locate Backup File dialog box, select the file that you want to restore, and then click OK.

8. In the Select the backup sets to restore grid, select the Restore check box next to the most recent full backup.

9. In the Restore Database dialog box, on the Options page, under Restore options, select the Overwrite the existing database check box.

10. Click OK to start the restore process.

To restore a backup copy of a database in SQL Server 2008 Enterprise

1. After you connect to the appropriate instance of the SQL Server 2008 Database Engine, in Object Explorer, expand the server name.

2. Right-click Databases, and then click Restore Database. The Restore Database dialog box appears.

3. In the Restore Database dialog box, on the General page, type the name of the database to be restored in the To database list.

4. In the To a point in time text box, retain the default (Most recent possible).

5. To specify the source and location of the backup sets to restore, click From device, and then click Browse to select the backup file.

6. In the Specify Backup dialog box, in the Backup media box, be sure that File is selected.

7. In the Backup location area, click Add.

8. In the Locate Backup File dialog box, select the file that you want to restore, click OK, and then, in the Specify Backup dialog box, click OK.

9. In the Restore Database dialog box, under Select the backup sets to restore grid, select the Restore check box next to the most recent full backup.

10. In the Restore Database dialog box, on the Options page, under Restore options, select the Overwrite the existing database check box.

11. Click OK to start the restore process.

Set the databases to read-write (database attach with read-only databases)

You must also set the databases back to read-write before you attach and upgrade them.

To set a database to read-write in SQL Server 2008

1. In SQL Server Management Studio, in Object Explorer, connect to an instance of the Database Engine, expand the server, and then expand Databases.

2. Select the database that you want to configure to be read-write, right-click the database, and then click Properties.

3. In the Database Properties dialog box, in the Select a page section, click Options.

4. In the right pane, under Other options, in the State section, next to Database Read-Only, click the arrow, and then select False.

To set a database to read-write in SQL Server 2000

1. In SQL Server Enterprise Manager, right-click the name of the database that you want to set to read-write, and then click Properties.

2. In the Properties dialog box, click the Options tab.

3. Under Access, clear the Read-only check box, and then click OK.

Verify custom components

Before you attach the content databases to the Web applications, use the Test-SPContentDatabase Windows PowerShell cmdlet to verify that you have all the custom components that you need for that database.

To verify custom components are available by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following command:

   Test-SPContentDatabase -Name <DatabaseName> -WebApplication <URL>
   

   Where:

   • <DatabaseName> is the name of the database you want to test.

   • <URL> is the URL for the Web application that will host the sites.

For more information, see Test-SPContentDatabase.

Attach a content database to a Web application

When you attach a content database, make sure that the root site for the Web application is included in the first content database that you attach. In other words, before you continue, examine the root of the Web application in the original server farm to determine the first site collection. After you attach the database that contains the root site, you can attach the other content databases for the Web application in any order. You do not have to create any site collections to store the content before you attach the database; this process creates the site collections for you. Make sure that you do not add any new site collections until you have restored all the content databases. When you created the Web applications in the new environment, a content database was created for each Web application by default. You can ignore these default databases until after you have attached your previous version databases, and then you can delete the default databases.

You can use either the Mount-SPContentDatabase cmdlet in Windows PowerShell or the addcontentdb Stsadm command to attach a content database to a Web application. Using the SharePoint Central Administration pages to attach a content database is not supported for upgrading.

Ensure that the account you use to attach the databases is a member of the db_owner fixed database role for the content databases that you want to upgrade.

To attach a content database to a Web application by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

5. At the Windows PowerShell command prompt, type the following command:

   Mount-SPContentDatabase -Name <DatabaseName> -DatabaseServer <ServerName> -WebApplication <URL> [-Updateuserexperience]
   

   Where:

   • <DatabaseName> is the name of the database you want to upgrade.

   • <ServerName> is server on which the database is stored.

   • <URL> is the URL for the Web application that will host the sites.

   • Updateuserexperience is the choice to update to the new user experience or stay in the old user experience (part of Visual Upgrade). When you include this parameter, the site is set to preview the new user experience. Omit this parameter if you want the site to remain in the old user experience after upgrade. For more information, see Plan visual upgrade (SharePoint Foundation 2010).

For more information, see Mount-SPContentDatabase.

To attach a content database to a Web application by using the Stsadm command-line tool

1. On the drive on which SharePoint Products and Technologies is installed, change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\14\Bin.

2. Type the following command, and then press ENTER:

   stsadm -o addcontentdb -url <URL> -databasename <DatabaseName>

   [-databaseserver <ServerName>] [-databaseuser <UserName>]

   [-databasepassword <Password>] [-sitewarning <SiteWarningCount>]

   [-preserveolduserexperience true/false**]**

   [-sitemax <SiteMaxCount>]

   [-assignnewdatabaseid] [-clearchangelog]

   Note

   When you set the preserveolduserexperience parameter to true, the sites in the content database keep the look of the previous version after upgrade. When you set this parameter to false, the sites are upgraded to the new look and feel. The default for this parameter is true, which preserves the old look and feel.
   This parameter is part of the Visual Upgrade feature. For more information, see Plan visual upgrade (SharePoint Foundation 2010).

   For more information, see Addcontentdb: Stsadm operation (Windows SharePoint Services).

Verification: Verify upgrade for the first database

After you have attached a database, you can use the Upgrade Status page in Central Administration to check the status of upgrade on your site collections. After the upgrade process is complete, you can review the upgrade log file to see whether there were any issues during upgrade. Also, you can review each upgraded site to find and address any issues with how the content is displayed. For more information, see Verify upgrade and review upgraded sites (SharePoint Foundation 2010).

To view the Upgrade Status page

• In Central Administration, click Upgrade and Migration, and then click Check upgrade status.

To open the upgrade log file

• The upgrade error log file and the upgrade log file are located at %COMMONPROGRAMFILES%\Microsoft Shared\web server extensions\14\LOGS. The logs are named in the following format: Upgrade-YYYYMMDD-HHMMSS-SSS-error.log and Upgrade-YYYYMMDD-HHMMSS-SSS.log, where YYYYMMDD is the date and HHMMSS-SSS is the time (hours in 24-hour clock format, minutes, seconds, and milliseconds). An example for an upgrade error log is Upgrade-20090415-132126-374-error.log, and an example for an upgrade log is Upgrade-20090415-132126-374.log.

  Note

  The upgrade log file includes the name of the content database being upgraded.

Attach the remaining databases

After you restore the first content database and verify the upgrade by reviewing the upgrade log file, you can continue by restoring and upgrading the next database or databases. You can attach multiple databases at the same time in separate Command Prompt windows to run multiple upgrades at one time. After you successfully restore and upgrade all the content databases, you can review the sites to make sure that they were upgraded correctly.

Verification: Verify upgrade for additional databases

After upgrading any additional databases, view the Upgrade Status page to monitor progress and verify that the upgrade process is complete. Review the log file to identify any other issues, and then review each upgraded site to find and address any issues with how the content is displayed. For more information, see Verify upgrade and review upgraded sites (SharePoint Foundation 2010) and Manage visual upgrade (SharePoint Foundation 2010).

See Also

Concepts

Troubleshoot upgrade issues (SharePoint Foundation 2010)

Other Resources

Resource Center: Updates for SharePoint 2010 Products