Export (0) Print
Expand All
3 out of 3 rated this helpful - Rate this topic

Attach databases and upgrade to SharePoint Foundation 2010

Published: May 12, 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:

note Note:

One frequent cause of failures during upgrade is that the environment is missing customized features, solutions, or other elements. Be sure that any custom elements you have to have are installed on your front-end Web servers before you begin the upgrade process. Use the pre-upgrade checker — and, for a database attach upgrade, also use the test-spcontentdatabase Windows PowerShell cmdlet — to identify any custom elements that your sites might be using. For more information, see Identify and install customizations in the article "Use a trial upgrade to find potential issues."

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.

note Note:

The part of the process in this article that is specific to moving a database from one computer that is running Microsoft SQL Server to a different computer that is running SQL Server is known as planned relocation. For more information about planned relocation, see Moving User Databases (http://go.microsoft.com/fwlink/p/?LinkId=148425).

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.

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.

Important Important:

You cannot upgrade a database that is set to read-only. If you are using a database attach with read-only databases, you restore a copy of the database and perform the upgrade on the copy. If you are not using this method, but want to set content databases to read-only temporarily while you back up the current data, make sure that you set the databases to read-write before you attach and upgrade the databases.

Important Important:

Be sure you have run the pre-upgrade checker before you perform this procedure. For more information, see Run the pre-upgrade checker (SharePoint Foundation 2010).

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 (http://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.

Important Important:

Do not use the following procedure if you are performing a database attach upgrade with read-only databases. To continue to provide your users with access to their content, you need to leave the databases attached, and follow the steps in the Restore a backup copy of the database (database attach with read-only databases) section later in this article to make a copy of the databases instead.

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 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 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.

Important Important:

If you move your databases to a different instance of SQL Server, make sure to verify that security is configured correctly. Check that the accounts you use have the appropriate fixed roles and permissions on the databases, and that they will still be valid accounts if you are moving across domains.

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 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.

Important Important:

If you are moving the content databases across domains or forests or into another environment that has different service accounts, ensure that the permissions for the service accounts are still correct before you attach the 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.

Important Important:

If you were using forms-based authentication, you will need to configure claims-based authentication for your Web application before you attach any databases. You must also create a policy to grant Full Control to the Web application to the user account that will be performing the database attach upgrade.

For more information, see Configure forms-based authentication for a claims-based Web application (SharePoint Foundation 2010).

Tip Tip:

You cannot attach the same content database more than once to a farm, even on different Web applications. Each site collection in a content database has a GUID that is associated with it, which is registered in the configuration database. Therefore, you cannot add the same site collection twice to the farm, even in separate Web applications. Although you can successfully attach the database in this situation, you will be unable to start the site collection.

If you need a duplicate copy of a site collection in the same farm, first attach the database that contains the site collection to a separate farm, and then use the Stsadm backup and restore operations to copy the site collection over to the other farm. The Stsadm backup and restore process creates a new GUID for the site collection.

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.

note Note:

We recommend that you use Windows PowerShell when performing command-line administrative tasks. The Stsadm command-line tool has been deprecated, but is included to support compatibility with previous product versions.

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.