Migrate databases

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

Topic Last Modified: 2016-11-14

In this article:

• Before you begin

• Set the previous version databases to be read-only

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

• Restore the backup copy to the new farm

• Add the databases to the Web applications

• Review the upgrade log files for any issues

• Repeat the restore and add database procedures for remaining databases

When you upgrade by way of a database migration, you essentially perform a backup and restore of your databases. That is, you back them up in the old farm, and then restore them in the new farm. When you restore a database and add it to the farm, the upgrade process runs and upgrades the entire database. The database migration process is similar to the in-place upgrade process; the difference is that the database migration process is performed manually and gradually.

You can migrate databases from either Microsoft Office SharePoint Portal Server 2003 or Microsoft Windows SharePoint Services 2.0 to your new Microsoft Office SharePoint Server 2007 environment. For both sources, the steps to set up and attach databases to your new environment are the same. The only difference is that for Windows SharePoint Services 2.0, you need to migrate only content databases — and no other database types.

After you have set up the new environment, you can follow the steps below to detach and then reconnect the databases to perform the upgrade.

Before you begin

Before backing up and migrating your databases, you must perform the following steps to prepare for the upgrade.

1. You must run the pre-upgrade scan tool on the content in your original environment. If you do not run the pre-upgrade scan tool on your databases before you back them up, you will not be able to upgrade the data on your new server farm. Be sure to use the configuration file option when scanning an SharePoint Portal Server 2003 environment. For more information, see Run the pre-upgrade scan tool (Office SharePoint Server).

2. Create your new server farm environment. For information about creating the new environment, see Prepare the new Microsoft Office SharePoint Server 2007 environment.

3. If you have custom site definitions or area definitions in your old environment, create new site definitions and upgrade definition files for these site definitions and deploy them to your new environment. For more information, see the following resources:

   • Develop new custom site definitions and create upgrade definition files (Office SharePoint Server)

   • Deploy upgrade definition files and new site definitions (Office SharePoint Server)

Set the previous version databases to be read-only

Be sure you capture all of the data in your backup so that you are restoring and upgrading the current state of your environment. Therefore, set the previous version databases to read-only so that users cannot add or change information in the sites. With the databases set to read-only, users can continue to view content, but they are not able to add or change content.

Set a database to read-only in SQL Server 2000

1. In Microsoft 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.

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 down arrow, and then select True.

Back up the previous version databases by using SQL Server tools

Follow the appropriate steps to back up your databases in either SQL Server 2000 or SQL Server 2005. Repeat these steps for each of the following database types in your server farm:

• Site

• Content

• User profile

You do not need to back up the configuration or component settings (search) databases, because you will re-create these databases in the new server farm. For more information about the types of databases you might have in your SharePoint Portal Server 2003 server farm, see Run and test a full backup in SQL Server.

Back up a database in SQL Server 2000

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

2. In SQL Server Enterprise Manager, click the plus sign next to Microsoft SQL Servers.

3. Click the plus sign next to SQL Server Group.

4. Click the plus sign next to (local) (Windows NT).

5. Click the plus sign next to Databases.

6. Right-click the database 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:

   1. Click Add.

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

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

Repeat these steps to back up the databases that are used by SharePoint Portal Server 2003 in your environment, except for the configuration and component settings (search) databases.

Back up a database in SQL Server 2005

1. On your 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 connecting to the appropriate instance of the SQL Server 2005 Database Engine, in Object Explorer, expand the server tree by clicking the plus sign next to the server name.

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

5. In the Source section, 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 section, 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 section, 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 preceding procedure to back up the databases that are used by SharePoint Portal Server 2003 in your environment, except for the configuration and component settings (search) databases.

Restore the backup copy to the new farm

After you have configured the new environment, you can restore the backup copies of the databases. Start with one database, and then verify that the restoration has worked before you restore the other databases.

You must create a new database to contain the restored data, and then you can restore the backup into that new database. When you create the new database, be sure to create a large transaction log initially. The upgrade process creates many transactions while it runs and if your log is small, it may not be able to grow quickly enough to handle the number of transactions. If the transaction log runs out of space, the upgrade process will fail and you will need to retry attaching the database and upgrading the data.

The following section provides steps for restoring the backups for both SQL Server 2000 and SQL Server 2005.

Restore the backup copy in SQL Server 2000

First, you create a new database. Then, you restore the backup into that new database.

Create a new database to contain the restored data in SQL Server 2000

1. Open Enterprise Manager and connect to the server to which the backup is to be restored.

2. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand (local) Windows NT.

3. Right-click Databases and then click New Database.

4. In the Name box, type the name of the database you want to restore.

5. On the Data Files tab, in the File Name box for the Data file type, verify that the name is the one you want to use.

6. In the Initial size (MB) box, adjust the size to approximately the size of the database you want to restore.

7. On the Transaction Log tab, in the File Name box for the Log file type, verify that the name is the one you want to use.

8. In the Initial size (MB) box, adjust the size to approximately three or four times the size of the log file for the database you want to restore.

   Make the log file very large to avoid filling it up during the upgrade process. You can always shrink the transaction log again after you have completed the upgrade.

9. In the File properties section, be sure that the Automatically grow file check box is selected.

10. In the Maximum file size section, be sure that Unrestricted file growth is selected.

    You can change these settings after you perform the upgrade, but again, you do not want to have the log file run out of space during the upgrade process.

11. Click OK to create the database.

Restore a backup copy of a database as a new database in SQL Server 2000

1. Copy the database backup file to the target computer or share it on the network so that it is accessible from the server.

2. Open Enterprise Manager and connect to the server to which the backup is to be restored.

3. Expand Microsoft SQL Servers, expand SQL Server Group, and then expand (local) Windows NT.

4. Right-click the database you just created, click All Tasks, and then select Restore Database.

   The Restore Database dialog box appears.

5. In the Restore as database box, select the new database.

   This is the database to which the backup will be restored.

6. Select the From device option.

7. Click the Select Devices button.

8. In the Choose Restore Devices dialog box, select Disk.

9. Click the Add button. The Choose Restore Destination dialog box appears.

10. Enter the file name of the backup file that is to be restored. If the file exists on a network share, provide the UNC share name in the File name text box.

    Note

    We recommend that you copy the backup to the server and use a local path to restore the database.

11. Click OK.

    The Choose Restore Device dialog box is now updated with the selected file.

12. Click OK to return to the Restore Database dialog box.

    The Devices list is now updated with the file selected in the previous step.

13. Click the Options tab and select the Force restore over existing database check box.

    This ensures that the restore operation overwrites the new database you just created.

14. Click OK in the Restore Database dialog box.

    This starts the restore process. Progress and confirmation dialog boxes are displayed as SQL Server performs and completes the restore of the database.

15. Click OK in the confirmation dialog box.

For more information about restoring a backup to a new database, see SQL Server 2000 Backup and Restore on the Microsoft TechNet Web site (https://go.microsoft.com/fwlink/?LinkId=93461).

Restore the backup copy in SQL Server 2005

First, you create a new database. Then, you restore the backup into that new database.

Create a new database to contain the restored data in SQL Server 2005

1. After connecting to the appropriate instance of the SQL Server 2005 Database Engine, in Object Explorer, expand the server tree by clicking the plus sign next to the server name.

2. Right-click Databases, and then click New Database.

3. In the Database name box, type the name of the database you want to restore.

4. In the Owner box, specify an owner if desired.

5. In the Database files section, in the Logical Name box for the Data file type, verify that the logical name is the one you want to use.

6. In the Initial Size (MB) box, adjust the size to approximately the size of the database you want to restore.

7. In the Logical Name box for the Log file type, verify that the logical name is the one you want to use.

8. In the Initial Size (MB) box, adjust the size to approximately three or four times the size of the log file for the database you want to restore.

   Make the log file very large to avoid filling it up during the upgrade process. You can always shrink the transaction log again after you have completed the upgrade.

9. In the Autogrowth column for the log file, set it to By 10 percent, unrestricted growth.

   You can change this setting after you perform the upgrade, but again, you do not want to have the log file run out of space during the upgrade process.

10. Click OK to create the database.

Restore a backup copy of a database in SQL Server 2005 Enterprise Edition

1. Right-click the database you just created, point to Tasks, point to Restore, and then click Database. The Restore Database dialog box appears.

2. In the Restore Database dialog box, on the General page, the name of the restoring database appears in the To database list box.

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

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

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

6. In the Backup location area, click Add.

7. In the Locate Backup File dialog box, select the file 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 checkbox.

10. Click OK to start the restore process.

Add the databases to the Web applications

When you add the content databases, be sure that the root site for the Web application is included in the first content database you add (in other words, the first site collection at the root of the virtual server in your old environment). After you have added the database that contains the root site, you can add the other content databases for the Web application in any order. You do not need to create any site collections to store the content before you add the database; this process creates the site collections for you. Be sure that you do not add any new site collections until you have restored all of the content databases.

You must use the Stsadm command-line tool to add a content database to a Web application.

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

To add a content database to a Web application, you use the addcontentdb operation.

Run the following command:

stsadm -o addcontentdb -url <URL> –databasename <database name>

[-databaseserver <server name>] [-databaseuser <user name>

[ -databasepassword <password>] [-sitewarning <site warning count>]

[-sitemax <site max count>]

The following table explains the parameters for the operation.

For more information about this operation, see Addcontentdb: Stsadm operation (Office SharePoint Server).

Review the upgrade log files for any issues

After you have attached a database and the upgrade process has completed, you can review the upgrade log file to see if there were any issues during upgrade. The upgrade log file and the trace log file are located at %COMMONPROGRAMFILES%\ Microsoft Shared\web server extensions\12\LOGS. The trace log is named in the following format: Computer_name-YYYYMMDD-HHMM.log, where YYYYMMDD is the date and HHMM is the time, for example, Server1-20061105-1241.log.

Repeat the restore and add database procedures for remaining databases

After you have restored the first content database and verified the upgrade by reviewing the upgrade log file, you can continue by restoring and upgrading the next database. After you have successfully restored and upgraded all of the site, content, and user profile databases, you can review the sites to be sure that they upgraded properly. For more information, see Review upgraded sites (Office SharePoint Server).

Next, follow the steps in Perform post-upgrade steps for an in-place upgrade (Office SharePoint Server). Because database migration is essentially an in-place upgrade for your content, you can use the same post-upgrade steps. For more information, see Chapter overview: Perform post-upgrade steps (Office SharePoint Server).

Download this book

This topic is included in the following downloadable book for easier reading and printing:

• Upgrading to Office SharePoint Server 2007

See the full list of available books at Downloadable content for Office SharePoint Server 2007.